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

In reply to Re^2: OT: benefits of database normalization by revdiablo
in thread OT: benefits of database normalization by revdiablo

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.