in reply to Re: OT: benefits of database normalization
in thread OT: benefits of database normalization
I have to say, I don't like this one much at all. It's marginally better than Design 1, because there isn't a column that changes meaning depending on another column's value. But the fact that either the parent or the fact are NULL in every record just makes for wasted space.
Also, in your design, just like Design 1, there is some data consistency logic that must be present in the application layer. I agree with others that this type of logic should almost always be encapsulated in the database engine. For instance, what happens if a fact has both a parent and a fact? You say your application won't let that happen? Well, maybe it was inserted by someone else's buggy code. No matter how it happened, there is now an inconsistency in the database. With Design 3, that cannot happen, because the database engine won't let it (assuming a sufficiently advanced database engine, but that's not really the issue here).
|
|---|