Working on the SQL

[j4]

Crowdsourcing my brain....

I am looking for a good introduction to sensible relational database design.

I have written Perl to interface with databases; I am happy with constructing reasonably simple SQL queries and can, with a bit of headdesking, understand the seriously mangled SQL which makes the course booking system work; but I have never built a complex database (ie anything much above "hello world" kind of level) from scratch.

Pretty much everything I know about databases has been inferred from querying them in one way or another, usually in fairly narrow ways; I feel a bit like two and a half blind men with approximately three eighths of an elephant (<pred>but where can I get one at this time of night?</pred>), and I am worried that my incomplete understanding will result in me getting trampled on by the elephant sooner or later.

What I want is to be able to look at a moderately complex database and, with some poking around (hopefully knowing what I was looking for!), understand a) how it all interlinks, and b) whether if I was building it again from scratch I would change it. I suspect that the latter will probably be partly a question of personal style.

I'm not really expecting clear rules about what makes a sensible database (though that would be nice), so much as something that will point me towards 'best practice' or at least 'how to avoid tying oneself in unspeakable knots'.

I would also like to know how people who do understand databases describe them, document them, map them so that the representation of them a) makes sense to normal people, and b) is unambiguous to abnormal people anybody who's dealing with the back end of the database (which is, I fear, a bit like the back end of an elephant). I suspect there will be more than one right answer to a) and/or b).

I have a map on my wall of how our course booking database works, though it's only the basic tables and not all the complicated logic. I suspect this is not how real people draw database structures.

Any suggestions, comments, constructive ridicule etc gratefully received.

Comments

[vatine]

I've always interpreted "duplicating data BAD" as meaning that it's too damned easy ending up with inconsistent data sets if you have data duplicated everywhere. If you need to duplicate data to get sufficient speed, by all means do this, but...

[rysmiel.livejournal.com]

This is what table constraints, integrity checks, and well-defined population procedures are for.