Database Design Theory?

Ross Werner ross at agilestudios.com
Tue Nov 8 18:25:48 MST 2005


On Tue, 8 Nov 2005, Levi Pearson wrote:
> 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

I think that's very excellent advice, but this sort of information (with 
each step made explicit with lots of examples, especially of common 
pitfalls and difficulties) isn't really what I saw in the database class 
at BYU. Now I didn't actually take the class, so I probably have no room
to talk, but I've spoken with several people who *have* taken the class 
and this sort of stuff, to the best of my knowledge, wasn't covered in 
much more depth than what you've listed here. Would anyone who has 
actually taken the class care to chime in and relieve me of my ignorance?

> 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.

One question I've been wondering about that maybe the SQL gurus can help 
me out with:

Let's say you have a table "foo" that needs to reference the "fruit" 
table. So you have a "fruit_id" column in table "foo". Now, each "fruit" 
can be either an "orange" or a "banana" but not both. Also, the columns 
required for each table are vastly different--an entirely different set 
for "orange"s than "banana"s. What's the best way to model this in the 
database?

Basically in object-oriented terms, "orange" and "banana" both extend the 
same abstract class "fruit".

What I typically do is have a "fruit" table, an "orange" table, and a 
"banana" table with the primary key of the "orange" table also being a 
foreign key to the "fruit" table, and ditto with "banana". Unfortunately 
this doesn't technically prevent (from the database standpoint) a 
particular fruit instance from being both an orange *and* a banana, and 
it's a headache when trying to figure out the types and information of a 
list of fruits.

Anyone have a better way?

 	~ Ross



More information about the PLUG mailing list