Can Child table from 1-many be a parent to another table thats many to 1

Question

1)GroupParent1 table( Gid, value) (111,Shirt)

2)GroupChild1(Gcid, Gid, value) (1,111, blue)(2,111, cotton) i.e. Stores attributes for products so a group of attributes in this table can have one or more row.

ISSUE: Another table called 3)Price should store price for " only" groups that exist in table 2 above using redesign/ PK-FK or both.

Like Blue, cotton shirt can have a price Or Red, Silk shirt can have another price.

In short, how can we enforce pk-fk constraint or redesign them so that Price can only be created if & only if both the other tables have data.

I can put Pk From table 1 to either of tables & can enforce referential constraint. But I am unable to use table 2 to enforce table 3 have entry only if the group has been created in table 2. As table 2 has group so I am unable to do that as a group has multiple rows i.e. 2 in this case.

Answer 1

You have 2 ways to do it:

• First (if one element of GroupChild1 table can only has one element), you can add one column to 'GroupChild1' to keep the price.

• Second (if one element of GroupChild1 table can has one or more prices), you should create a table called 'Prices' with a relation with 'GroupChild1'.

  [Price] -- {id, price, groupchild1_id}

I hope it will help you.