Database Design Theory?
levi at cold.org
Tue Nov 8 17:34:20 MST 2005
On Nov 8, 2005, at 4:49 PM, Ross Werner wrote:
>> From my limited experience (*couBYUgh*) college/university database
> classes speak far more about the mathematical theory of databases
> (as well they should) than helpful real-world design principles
> regarding views, stored procedures, foreign-key constraints, when
> to separate data into different tables, when to keep it in a single
> table, etc. etc.
> However, I don't know where good information on this subject can be
> found. There's all sorts of good design information for software on
> the web, from UI to class structure, but I don't know of any for
> databases. Let me know if you find anything!
> ~ Ross
Well, I'm not sure which class you took, but the theory of databases
has quite a bit to do with foreign key constraints and how data
should be separated into tables. Normalization techniques are just
as much a part of database theory as relational algebra/calculus; the
former tell you how to design the tables, while the latter tell you
how to retrieve what you need from them.
As usual, Wikipedia has some useful information on database principles:
Here's what looks like a reasonable intro to database design: http://
I'd say the general technique to follow is thus:
Exhaustively catalog the entities that you wish to model and their
relationships to one another
Draw up an ER or UML diagram of the entities and relationships
Convert the diagram to a database schema
Normalize the schema
Convert schema and constraints to SQL
Given all that background information, it should be fairly simple to
Google for topics that are weakly covered in the above documents.
Eventually, it will also become important to learn about the ACID
properties, how database indices work, and other related topics.
This is all fundamental stuff, so there's plenty of information on it
around on the web.
More information about the PLUG