I was browsing a book store the other day and I picked up a book about database design (I can't recall the name). I flipped through the index and it seemed reasonable. There were interesting points regarding using surrogate keys for tables (I prefer surrogate keys, but not everyone does and there are valid arguments each way). I saw some great examples of database design flaws and how to correct them, but I was a caught a bit short when the author wrote that the two reasons for having a NULL in a column are when the data are unknown or inapplicable. There appeared to be absolutely no discussion about how the presence of NULLs can lead to incorrect query results or how to provide greater information about why the NULL is in the database in the first place. Still, this is about par for database books, so I didn't pay it a lot of attention.
The discussion about normalization came rather late in the book, but the author explained that this was because normalization had already been applied throughout the book, but now they were going to get to the underlying rules. OK, that sounds like a valid approach. Then they recap what they've learned before:
The book went back on the shelf. This was supposed to be an implementation agnostic database design book.
Re:Inheritance is supported by...
Ovid on 2007-04-23T21:25:46
... sucking up to your rich uncle.
Where there's a will, there's a relative.
Seriously, the book was suggesting using other tables for subclasses and foreign key constraints against the original table. This, however, can lead to very clumsy code, though in practice, this is what is generally done. What if you want to save changes to a class? You class code needs to keep track of all tables which are affected and update them accordingly. I've done this and it can get quite complicated.
That being said, the original relational model (not what we find in databases today) solves this problem trivially. Let's say that you have an Animal relation (sort of like a table) and a Mammal relation. How would Mammal inherit from Animal?
What you could do is rename Mammal to _Mammal and make the subclass by writing a join which selects all of the relevant data from Animal and _Mammal into a new relation called Mammal. In the original relational model, the new relation is
... wait for it ... a relation! It's completely identical in behavior to the relations it's derived from and you can select from it, write to it, delete from it and have those changes appear in the original relations. SQL databases don't handle this very well but David Wheeler's module Object::Relation does just that by using triggers and rules on either PostgreSQL or SQLite views (MySQL still doesn't support this) to allow you to treat views as tables. Thus, you can compose classes into a view and act like all of your data for a class is stored in a single table, regardless of how many underlying tables there are. It's quite slick, but it's not documented terribly well.
Disclaimer: I did a fair amount of hacking on that code, so I may be biased. All credit for good stuff there belongs to David.
