Database Design Theory?
hanksdc at plug.org
Tue Nov 8 16:38:13 MST 2005
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
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
- 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).
More information about the PLUG