If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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?

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.

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

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.

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?

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.