Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

comment on

( [id://3333] : superdoc . print w/replies, xml ) Need Help??

ksublondie:

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....

...roboticus

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


In reply to Re: perl & SQL best practices by roboticus
in thread perl & SQL best practices by ksublondie

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.