Post reply

changing design of table to n-n

  • 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.

     

  • 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.

     

    -

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply