Database structure question: Table with a relationship to grand-parent/parent/child tables

I'm developing a traceability system that records certain activity in the manufacture of a multi-level item. The grand-parent item consists of 2 or 4 parent items, which in turn consists of 2 child items.

At each level, at various points in the manufacturing process, leak tests are conducted on all three types of item - the information recorded against each will be the same: Leakage rate, pass or failure flag, timestamps etc.

So do I opt for one LeakTests table, with a generic FK field to hold the ID of the item the test relates to, along with an indicator to show which table the FK refers to:

PK: LeakTestID, Int
FK: LeakTestItemID, Int
FK: LeakTestTypeID, Int
LeakageRate, Float
Result, Bit

Do I store FKs for each type in a different field?

PK: LeakTestID, Int
FK: ChildID, Int
FK: ParentID, Int
FK: GrandparentID, Int
LeakageRate, Float
Result, Bit

Or do I opt for 3 x Leak test tables, one for each level of item.

I can see various advantages and disadvantages with each, and I've changed my mind several times.

Any thoughts? I'm afraid that the requirements for this system are barely defined, and part of my job is to wrestle with the business and the downstream customers to pin both sides down. But as it is, I can't be sure how the data will be used and if/how the requirements are likely to change.

Clarification: I think the g-parent/parent/child bit has thrown people - the 3 tiers are not identical tiers, they are entirely different, eg:

ATS:
PK: ATSID, Int
MeasurementA char()
MeasurementB char()
MeasurementC char()

SleevedPair:
PK: SleevedPairID, Int
MeasurementD char()
MeasurementE char()

SleevedItem:
PK: SleevedItemID
MeasurementG char()
MeasurementH char()
MeasurementI char()
MeasurementJ char()
MeasurementK char()

...it just happens tha开发者_运维知识库t each object has one or more leak tests associated with them.

---

There are certainly advantages to each, but I think I like your last option, using a leak table for each level of object. Here's my reasoning:

Your first structure, which identifies a typeID and itemID is decent but would result in a larger, harder to use table than 3 separate ones.

The second option would have sometimes unnecessary fields. For ever grandparent level object you would have to log nulls for parent and child, likewise for every child you would have to log both the parent and grandparent, which is presumably information that exists elsewhere.

I'm no expert though, just my two cents.

---

My grandparent (Steve) is my parent's (Anne) parent.

ID     Name     ParentID
1      Steve
2      Anne       1
3      Steph      2
4      Amy        3
5      Sue        3

There's no reason to store

ID     Name     ParentID  GparentID ChildID
1      Steve
2      Anne       1
3      Steph      2         1         4
4      Amy       ....

I can derive my grandfather Steve by checking my parent's parent. Likewise, there's no need to store my daughters are Amy and Sue because I just check the parentID column in the first table for my ID (ParentID = 3). This is ultimately flexible if these ambiguous requirements change. The only thing which the hierarchy won't support is multiple parents. Unless every node can have at most one child then you just flip the tree and define the parentID as ChildID and then you can have multiple parents per child. But only one child per parent . If you need many parents and many children you need a many-to-many mapping table.