Database Design Theory?
hanksdc at plug.org
Tue Nov 8 20:01:51 MST 2005
On Tue, 8 Nov 2005, Ross Werner wrote:
> 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?
When I was at BYU the class was CS 452 (I think), and it covered ER
modeling, and translating ER models to relations etc. It also covered
normalization very thoroughly. CS 453 went into physical implementation of
databases, covering topics such as B+-tree indexes. Fun stuff. It would be a
sad day indeed if BYU had stopped teaching these fundamentals in a rigorous way.
> 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".
In a perfect world you'd be able to create declarative database-level
constraints that would prohibit something being both an orange and a
banana at the same time. As was mentioned in another post, about the only
thing that comes close to that is a trigger, but that would be a
procedural (and therefore sub-optimal) solution.
Date covers such constraints in "Database In Depth".
More information about the PLUG