changing design of table to n-n

• Vishal-311615

  SSC Rookie

  Points: 31

  More actions

  June 15, 2006 at 5:45 pm

  #171385

  I have a table:

  Product_id(parent_id), product_ref_id(child_id), product_name

  2             0               clothes

  3             2               pants

  4             2               shirts

  5             2               coats

  6             3               jeans

  8             6               boot cut

  Now here for each product_id there can be only one product_ref_id

  I want to change my table, maybe create another table so that

  I can have design like this when people search my site

  Clothes--pants--jeans--boot fit

  Clothes--pants--jeans--relaxed

  or just like

  jeans---relaxed

  jeans---loose fit

  or

  shirts------long sleeves-cotton

  clothes---shirts---long sleeves---denim

  shirts---black---cotton

  so jeans being a child of clothes can also be a parent directly.

  So each parent can have multiple children and each child multiple children. and each child can have further children.

  But my current table restricts each child to have only one parent and no further children.

  I can create another table with colums parent_id, child_id

  Any advice or suggetions on this one.

   

• Jason-

  SSCrazy

  Points: 2795

  More actions

  June 15, 2006 at 9:48 pm

  #644127

  without redesigning your db structure to 3nf I would change the product_id and product_ref_id to binary values, it will allow you do bitwise comparisons:

  Your product_ids will be:

  0x0000  Clothes

  0x0001  Pants

  0x0002  Shirts

  0x0004  Coats

  0x0008  Jeans

  0x0010  Boot Cut

  Your Product_ref_id for clothes which encompases all will be:

  0x0001F  Which is 0x10+0x08+0x04+0x02+0x01 (aka 31)

  This allows you to search like this:

  where Product_Ref_Id&0x04 = 4

  this will return any result that was related to coats.

  For more info do a seach in BOL for bitwise.

   

  btw:  you have here what is called an "inter-row dependancy" where a column or multiples columns in a single row are dependant upon another row in the same table, this makes data integrity a volitle thing, you really should store your values in a seprate table something like a category table and then a products table where your products are assigned the binary category...  or go further, create a productscagegory table with fk to products and categories.

   

  -