0

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.

Hem
  • 130
  • 2
  • 12

1 Answers1

0

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.

Grommy
  • 367
  • 1
  • 8
  • Hi Grommy, one group of GroupChild1 table can have one or more elements but that whole group can have only one entry in Price table.Hence the design issue to enforce a price entry only if group exists.Thanks – Hem Jul 23 '16 at 14:55
  • For e.g. Group - Blue cotton of Shirt is $ 30 price whereas another group BLue silk short is $ 25 both bave parent as shirt. – Hem Jul 23 '16 at 15:00
  • First option would work if a group has one element as you stated , but it has one more elements in the group – Hem Jul 23 '16 at 15:07
  • If you can have 'Blue + Cotton'(combination) it has an N-M relation with another table like 'Colors'. Then do that relation and in the resultant table add column price. (in N-M table for example GroupChild1_colors). But you should take a look with 'Nested model' to order categories and products. – Grommy Jul 23 '16 at 15:12
  • Yes, I have stored blue colors etc attributes in diff table so this Groupchild has FKs. I've just using above tables to show th Problem i.e a group table has Gpid repeating and price needs to be set for every GPid (blue cotton)and not for each of its attributes( just cotton). If I were to create a view to take distinct Grpid from groupchild and make that View as parent of Price table then it would have worked but thats bad design & not sure Mysql allows view & table pk-fk but I guess u see the prob now. – Hem Jul 23 '16 at 15:37
  • Also if there is a new attribute like formal, casual then one can have shirt Grey, cotton, formal or red, silk, casual. Both of these shirts will have its own price. – Hem Jul 23 '16 at 15:46