the meaning of the "data" column depends on a non-key column. This would then appear to be a violation of 3NF
Ah, thank you for spelling that out. This is something I was still curious about. I have read the formal normalization rules many times, but actually seeing which applies where is still difficult for me. Seeing a problem in action, then knowing what it violates, is very helpful.
"design 3" more accurately captures the data model
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. :-)
| [reply] |
Howdy!
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.
| [reply] |