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