I've stumbled across a database design problem that's out of my league, and my go-to DBA guru is off on fire drills.
In essence, I have a table with the following primary key (PK for brevity):
child_id integer
parent_id integer
date datetime
child_id
and parent_id
are foreign keys to entity tables. The "child" table itself also contains a foreign key to the "parent" table, and lo, each child_id
always references the same parent_id
as expected by the table above. In fact, turns out there's some extra code keeping the two in sync.
Which makes this overenthusiastic normalization novice say "I should remove the redundancy instead!"
I decompose to the following:
Table_1 PK:
child_id integer
date datetime
Table_2 PK:
parent_id integer
date datetime
Table_3: (already exists)
child_id integer PRIMARY KEY
parent_id integer FOREIGN KEY
And lo, when I join these guys together in the natural way, I recover the original table. It's my understanding that makes this 5NF.
However, now I realize there's a hidden business rule.
Normally, the dates associated with a given child_id
must be a subset of the dates associated with the corresponding parent_id
. You can see that the first table enforces this rule.
My decomposition does not enforce the rule, because you can freely add to Table 1 until the dates get too large.
Which leads me here, with the following questions:
Is this decomposition 5NF? While I'd say it permits insertion anomalies, it also seems to follow the Wiki example, which itself follows this guide. The phrase (emphasis mine) "we can reconstruct all the true facts from a normalized form consisting of three separate record types" gives me special pause, since no matter how much garbage I pump into
Table_1
, the natural join still ignores it.Supposing I don't like this decomposition (I don't). I freely acknowledge that the practical solution is to leave the table and code as they are. But, in theory, is there a way to decompose and/or add constraints such that I get away from the first table and preserve my business rules?