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
/*
PLUG: http://plug.org, #utah on irc.freenode.net
Unsubscribe: http://plug.org/mailman/options/plug
Don't fear the penguin.
*/
