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:
child_id integer parent_id integer date datetime
parent_id are FKs to entity tables. The child table itself also contains a FK 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:
Question 1: 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.
Question 2: 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?