I agree with you on Design 1 that, while having the context of 'fact' change based on 'type' may work, I think it is going to cause more problems down the road than Design 2. If you are going to do validation on 'fact', you need to check what the 'type' is. If you do any aggregates/statistics on 'fact', you need to make sure you build your query so that type #2 is included properly. It can be done, but it seems like more work to me.

Personally, I would work Design 2 more along the lines of:

Design 3

fact_id fact
------- ----------------------
1       foo is a fooity foo
2       bar is a barrish baaar

name_id name fact_id
------- ---- -------
1       foo  1
2       bar  2
3       foog 1

[download]
It may just be a personal preference, but I like to keep all my names in one place, and all my facts in one place. I'm interested whether you had any reasons to use Design 2 rather than Design 3?

In reply to Re: OT: benefits of database normalization by Anonymous Monk
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.