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

[barnacle.livejournal.com]

Normalization - which is the de-duplication of data - definitely helps optimize your databases performance, but at the cost of complicating your application. It has to hold more table joins and conditions in its declarative layer, to reassemble the data into something meaningful. The level of normalization should support the application, and you can always (with some pain) normalize more in future.

The Art of SQL is a fascinating, surprisingly hard-headed book. But it's not a great beginning book, and there's only one or two large bits that I've had the patience to sit down and read straight through. The O'Reilly books for the SQL variants are generally OK.

Your db diagram looks fine, j4. You might want to flip the arrowheads, so they look like cartoon snowmen arms and hands. Then the single line means "one from this table" and the multiple line means "can be joined to many from this". Putting a line across the single end (so making it a cross) means it's specifically one-to-many (a line being a stylized 1) and a circle means zero-to-many. But that's just disambiguation.

Outside of just getting data in and out, hardcore optimization comes from knowing about indexes (and sequences) and, if adding indexes on foreign keys doesn't work its magic, being able to figure out the query plan output from the SQL client of your choice, and maybe just changing the order of your table joins to give the planner the hint it needs.

If you're using PostgreSQL, learn about these things; if you're using MySQL, just keep plugging away until performance starts to suffer in twelve months' time, then learn about them; if you're using Oracle or SQL Server, then set fire to the server and head to G&D's while the sirens come and go.

[(Anonymous)]

I'll second The Art of SQL as an excellent book to read eventually, though not beginner-friendly.