Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Re: perl & SQL best practices

by roboticus (Chancellor)
on Apr 30, 2012 at 22:50 UTC ( [id://968173] : note . print w/replies, xml ) Need Help??

in reply to perl & SQL best practices


When designing databases, I tend to:

  • Use stored procedures for all programmatic data changes (inserts, updates and deletes).
  • Provide predefined views for data.
  • Restrict access to the tables (including select).
  • Constrain the hell out of it.

It's pretty restrictive, but I think that's a good thing early on in the life of the database. If I find a better way to organize the data, I want the flexibility to do so. Early in the life of a database, I find myself restructuring tables, renaming columns to clean things up, etc. But as the database ages/stabilizes, you won't want to change the structure nearly so often. At that point, I feel it's perfectly reasonable to open up the select privileges on tables. However, I still restrict data changes to the stored procedures.

After all, you won't code all of a large system perfectly the first time through. I fully expect that a database design will also warrant improvement once the rubber hits the road. You'll find that a table over here should be split, some tables over there may be merged, etc. But the more publicly accessible structure you have, the "wider" the interface to the database. As you have more programs accessing the database, anything publicly accessible becomes "locked down" and unchangeable. Then you'll constantly be stubbing your toes against ugly parts of the database.

You can often make major changes to a program to fix architectural problems, but databases seem to have a habit of living longer than expected, and having more and more programs connecting to them. If they're unconstrained, they're *much* more difficult to change without risking breakage to systems you don't know about or have forgotten about.

By locking things down, at least in the early stages, you have a chance to alter the database structure as you learn more about the problem. I find the minor headaches involved doing it this way to be less irritating than the major headaches involved when having to change things and not know what may be affected. I'm sure I'm in the minority, as I've been involved in plenty of database projects, and I've not encountered anyone who I thought took it too far. (So I suppose I'm the guy that takes it too far.) But I'm in the position of not only getting to make those choices, but also the one most affected by them. (Since I'm the guy everyone in my department comes to for assistance in getting database work done and installed.)

If you create well defined views and stored procedures for data updates, you can make the database easy enough to work with that changing some back-end workings needn't be traumatic.

Ultimately, I'd suggest two things: (1) Lock down everything as tightly as possible as you learn your way throughout the system and the business problems you need to solve, (2) read plenty, especially things with data to back them up (unlike this node!), and then (3) loosen things up as you find appropriate.

When writing a node like this, I often find myself forgetting a myriad of details, and wishing that I could write it more clearly. But at some point, you've gotta hit either "create" or cancel the page....


When your only tool is a hammer, all problems look like your thumb.