Database Design Theory?

[Levi Pearson]

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:
http://en.wikipedia.org/wiki/Database
http://en.wikipedia.org/wiki/Relational_model
http://en.wikipedia.org/wiki/Database_normalization
http://en.wikipedia.org/wiki/Entity-relationship_model
http://en.wikipedia.org/wiki/Relational_algebra
http://en.wikipedia.org/wiki/Relational_calculus

Here's what looks like a reasonable intro to database design: http:// 
www.edm2.com/0612/msql7.html

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.

		--Levi