Indeed, I like Design 3 more than my own Design 2. I feel kind of silly not thinking of it right away, but to my own defense, Design 2 was an evolution of what I'll now call Design 0. In Design 0, there were no aliases, only facts. When the idea of aliases came up, I proposed adding another table, and someone else proposed adding another column to the existing table. Thus, this node was born. :-)
This makes Design 3 even more clearly the Right Way To Go.
Adding the elided step, having more than one name for the same fact could be done by simply adding multiple rows with the same fact, but a different name. IIRC, that could be a violation of 2NF, so you extract the "fact" column to a separate table and replace the "complicated" value with a simple key and rely on foreign key constraints to keep things honest. If the "fact" changes, you change one datum in one place to accomplish that. If you want to delete a "fact", either you cascade the delete to kill all the names that refer to it, or you require that the names be nuked first. Adding/changing/deleting names for a given fact is trivial.
Once the relationship between "name" and "fact" goes beyond one-to-one, you have to factor out the "many" side into its own table that refers to the "one" side.
...now if the rules change so that a name could refer to more than one fact, you have to make a third table that relates names to facts...but that is probably absurd for the situation at hand.
In reply to Re^3: OT: benefits of database normalization
by herveus
in thread OT: benefits of database normalization
by revdiablo
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |