Does a foreign key need to appear in children of children?

Question

I have a set of tables with children of children, like so:

Clients (PK ClientID) which is parent (one to many) to

Property (PK PropertyID, FK ClientID) which is parent (one to many) to

Property Detail (PK PropDetailID, FK PropertyID) and Case (PK CaseID, FK PropertyID).

Should the foreign keys for the parent tables be repeated further down? That is, should my tables look like this:

Clients (PK ClientID)

Property (PK PropertyID, FK Client ID)

PropertyDetail (PK PropDetailID, FK PropertyID, FK Client ID)

Case (PK CaseID, FK PropertyID, FK ClientID)

instead? And if neither setup is normalized, what's the normalized way to do this?

Answer 1

You don't need to have both Foreign keys for PropertyDetail/ Case. These ones can be navigated to.

Answer 2

No, the foreign keys should not be repeated because you can access this information with a simple join. Adding it to the grandchildren adds redundancy which can give problems when the two get out of sync. Your first design looks better than your second.

Depending on the meaning of the word property it could be that you are using an entity attribute value (EAV) model to store client properties. There are some situations where an EAV model is appropriate but in general you should try to avoid it. If possible try to use a fixed schema instead.

Further reading:

• EAV FAIL

Answer 3

There's no need to have the foreign keys repeated further down -- you can determine a property detail's ClientID by looking at the Property's ClientID.

All the information you need can be determined by simple joins.