Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    39

    Unanswered: Hierarchical data

    Say i have products and each product comes in different colors. It could be a single color or combined colors. So example..product A comes in white, blue and red+orange.... how do i create a table like that?

  2. #2
    Join Date
    Sep 2011
    Posts
    39
    is it alrgiht to store a color combo in one column..for stance

    COLOR
    ColorID
    Red
    Blue
    White,Silver
    Blue,Pink,Yellow


    I'm thinking of doing that....

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If your dataset is small, you could do that. But if you have millions of rows, consider how you would write a report that showed all pink items. A more traditional way to do this is to have a child table of colorid and color.

  4. #4
    Join Date
    Sep 2011
    Posts
    39
    cool..Yeah its a small database..

    although.. im curious how you would do parent child relationships for a bigger database..how would you do that? is it complicated?

  5. #5
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Quote Originally Posted by caa5042 View Post
    cool..Yeah its a small database..

    although.. im curious how you would do parent child relationships for a bigger database..how would you do that? is it complicated?
    Product
    ----------
    ProductID (PK)
    ProductName
    .....


    Color
    --------
    ColorID (PK)
    ColorDesc


    ProductColor
    ------------
    ProductID (FK)
    ColorID (FK)
    Cheers....

    baburajv

  6. #6
    Join Date
    Sep 2011
    Posts
    39
    Yes baburajv, thats the relationship that i currently have...but i was trying to figure out if there was a way to avoid putting "Blue,Red,Green" in one column ( for items that are a combination of different colors).....nevermind though...ill stick with the many to many relationship

  7. #7
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Assuming that you have a product which is available in 3 colors say RED,BLUE and GREEN, you can avoid many-to-many relation by using a bit map value for colors

    Let's add a new attribute in Product table, say, ColorBitMap TinyInt and assume that the bit positions in this attribute maps to the following colors

    Bit 7 -- WHITE
    Bit 6 -- YELLOW
    Bit 5 -- PINK
    Bit 4 -- BLACK
    Bit 3 -- ORANGE
    Bit 2 -- GREEN
    Bit 1 -- BLUE
    Bit 0 -- RED

    If a product is available in 3 colors, say RED, BLUE and GREEN,
    store a numeric value which has 1s in these bit positions, (in this example, the value will be 00000111 = 7) If the product is available in RED and BLUE only, store the value, 00000011= 3.

    This way you can avoid many-to-many-relation, but requires additional processing while storing and retrieving the color bit map value.

    NOTE: I am not sure of the limitations of this approach and whether this complies to the best practices in db design.
    Cheers....

    baburajv

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    the bitmask approach suffers because you can't usefully index a bitmask column. how would you write a query to give all the green products? you have to use bitwise operators which will cause table scans. if not many rows then this doesn't matter so much. if millions of rows, not so good. you could create computed columns for each of the bits i guess, and index those though. bleh.

    other drawback is transparency - bitmasks are often nothing more than magic numbers. 5 yrs from now some maintenance engineer will come across your mask and wonder wtf does 7 mean here?
    Last edited by jezemine; 09-14-11 at 04:56.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Maybe you can't index it, but bitmask operations are extremely fast.
    I think this is a good candidate for using a bitmask.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Any operation becomes slow on large tables, no matter how fast it might be on a single row.

    Any design that forces table scans (like a bitmap) when you can easily change the design to use index seeks (such as baburajv's suggestion) should be converted to use the index seeks as soon as is practical.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •