I was having a discussion with someone the other day, and was having a hard time thinking of the practical benefits of one database design over another. I was advocating the more normalized version, whereas the other party was advocating the non-normalized one.

Consider these two designs:

Update: these designs attempt to store a list of facts (think of dictionary definitions), and aliases. A fact is comprised of a word (what I called name), and a definition (what I called fact). There are also aliases, which have a word (again, name), but no definition. They point to another fact for the definition. This way, when the original fact is changed, the aliased one appears to be changed too.

To my eyes, Design 1 is not normalized to a satisfactory extent (i.e. none at all). The data column's meaning changes depending on the value of the type column. Even though I am not well-versed enough to cite what principle of normalization this violates, it feels wrong to me.

Despite this intuitive feeling that it's "just wrong," I had a hard time articulating what about it is wrong. I could not think of any practical reasons to use Design 2. I cited what I consider a powerful abstract reason, separation of cocerns. To me, Facts and Aliases are different types of values, and should not be shoehorned into the same table. The other party did not agree with this assessment, and continued to demand practical reasons.

So I come to you, my fellow Monks, in search of answers. Perhaps I am dead wrong, and the original design is perfectly acceptible? Maybe there is a more powerful abstract reason I can not think of? Or perhaps there are even some good practical reasons? Any responses will be greatly appreciated.

---

---

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! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

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:
	&		&
	<		&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.