parent child table design

• Raghavendra-499237

  Default port

  Points: 1435

  More actions

  December 14, 2015 at 8:30 am

  #310120

  Hi,

  I have a question about parent child table design. There is a parent table which relates 'n' number of child tables.

  tblParent

  tblChild1

  tblChild2

  ..

  tblChildn

  Normally primary key of tblParent will be referenced in all child tables as a foreign key. But I would like to relate child tables with parent table in reverse order by adding a column in tblParent for each and every child tables as given below.

  tblParent columns

  P_ID (pk)

  ch1_ID(fk1)

  ch2_ID(fk2)

  ...

  chn_ID(fkn)

  Is this a right design for a high volume database? if not let me know what are the issues with this approach?

  thanks in advance

  Raghu

• stevenb 14609

  SSCommitted

  Points: 1835

  More actions

  December 14, 2015 at 8:45 am

  #1845829

  First things first, this breaks normalization and there are other ways to do it. If you have a one to many relationship with parent to child, you're going to have a bad time because you look like you're only able to store one child id per child table in the parent table.

  The option that is usually used is to have a table that has 2 fields, ParentID and ChildID. Then you can search on parentID and get all the Children from that parent ID in this table.

  Raghavendra-499237 (12/14/2015)

  ---

  Hi,

  I have a question about parent child table design. There is a parent table which relates 'n' number of child tables.

  tblParent

  tblChild1

  tblChild2

  ..

  tblChildn

  Normally primary key of tblParent will be referenced in all child tables as a foreign key. But I would like to relate child tables with parent table in reverse order by adding a column in tblParent for each and every child tables as given below.

  tblParent columns

  P_ID (pk)

  ch1_ID(fk1)

  ch2_ID(fk2)

  ...

  chn_ID(fkn)

  Is this a right design for a high volume database? if not let me know what are the issues with this approach?

  thanks in advance

  Raghu

• Raghavendra-499237

  Default port

  Points: 1435

  More actions

  December 14, 2015 at 8:55 am

  #1845836

  There is no one to many relationship with parent to child, all are one-one.