Database Design Theory?

[Dan Hanks]

On Tue, 8 Nov 2005, Matthew Ross Walker wrote:

> My day job is programming in PHP with MySQL databases, and with the
> release of MySQL 5, I'd like to get familiar with Good Design for a
> system that has views, stored procs, foreign-key constraints, and the like.
>
> Are there any good websites for this information, that take you through
> practical examples, and explain the theory behind it? Alternately, are
> there any very good books on the subject? I'm a bit leary of dropping
> $50+ on a book that I'll only use for a while, but I'm willing to do it
> if it comes highly reccomended.

I'd recommend anything by C.J. Date. O'Reilly's "Database In Depth" by him 
is a good place to start, and is available for $24.50 at bookpool.com.

Read through as much as you can on www.dbdebunk.com.

As has been suggested, take a course about databases that teaches you 
about the relational model, and the underlying concepts of set theory and 
predicate logic.

The above won't hand out a lot of 'practical examples', but will get you 
grounded in the fundamentals of what databases are, which should be the 
first step to learning good design habits.

A couple of pointers I'll hand out that I've learned to live by from 
studying the above:

- Avoid nulls as much as possible
- Normalize
- Don't denormalize for performance unless you fully understand the 
implications of doing so 
- Make sure every table has a key
- Don't allow duplicate rows in any table (defining a key on each table 
will prevent this--a key is not the same thing as an index, keep in mind).
- Use constraints and foreign keys to enforce integrity (not application code).
- Every piece of information in your database should live in one and only 
one place (Factor out redundancy through normalization, etc).

Fwiw,

-- Dan