Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

child_id and 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?

share|improve this question
    
What are the key(s) in your original table? What dependencies is it supposed to satsify? You seem to be saying that child_id->parent_id, in which case child_id and parent_id cannot both be part of the same key in that table. –  sqlvogel Jun 28 '11 at 21:19
    
@trevor: Did you ever review the answers here? Last seen 19 minutes after asking. The answers came later. –  gbn Aug 27 '11 at 12:44
add comment

2 Answers

Normalization is based on functional dependencies. Functional dependencies have to do with semantics; they have to do with what the data means. When you simplify a real-world problem to the level of "parent_id, child_id, date", and you don't include any sample data, you really limit how much help a conscientious database designer can give you.

The fact that you have a key {child_id, parent_id, date} in one table, and that you have (it seems) a unique pair {child_id, parent_id} in the child table doesn't necessarily mean that part of the combination is redundant. It might mean that in the table that has {child_id, parent_id, date} as the primary key, the pair of attributes {child_id, parent_id} ought to reference the child table in the first place.

If that's the case, you might use FOREIGN KEY (child_id, parent_id) REFERENCES child (child_id, parent_id). To do that, you need a UNIQUE constraint on the pair of columns (child_id, parent_id) in the table "child", which shouldn't be a problem if child_id is its primary key.

But there's no way to tell without knowing what the data means, and you're the only one in this thread who knows that. (But we'd be happy to let you explain it to us.)

As far as the original table is concerned, you seem to be saying that child_id -> parent_id. If that's the case, why is parent_id in the original table in the first place? Why isn't the key just (child_id, date), with a foreign key reference to the "child" table? It looks to me like the kind of redundancy you're talking about might be resolved by dropping the column "parent_id".

SQL DDL and sample data in the form of INSERT statements helps us help you. DDL and INSERT statements are more precise than descriptions.

share|improve this answer
    
+2 for "functional dependency" reminder –  jcolebrand Jun 30 '11 at 4:14
add comment

Try this...

  • Add unique constraint to (child_id,parent_id) in the child table
  • Your current table (PK,FK:child_id, PK,FK:parent_id, PK:date) stays as is, the FK is on 2 columns to the new unique constraint

or

  • Remove the FK from the current child table
  • Create a new table (PK,FK:child_id, FK:parent_id) that is 1:1 with child
  • Your current table (PK,FK: child_id, PK,FK: parent_id, PK:date) stays as is. but the FK is on 2 columns to the new table

If nothing else, it may inspire you...

If I've understood correctly, it will remove redundancy and code...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.