rahul rahul - 13 days ago 6
SQL Question

Is this database design ok?

I am developing a mvc app for a carpet company. A carpet's price will depend upon color combinations and size. So to model this scenario i am using 2 tables.
1.Carpets.
2.CarpetVarients.(For various color and size combinations).

In CarpetVarients table i am thinking about creating columns "Colors", "Size" and "Price" (along other columns). Colors column will store comma delimited colors(like Cream,Red,).

Will this design work? or should i make every combination a different carpet.

Answer

Generally, storing things as delimited strings is not a good idea (unless you are talking about a carpet that has multiple colours in which case things might be different). Better to have a row for each colour/size combination.

Maybe it would be better to think of what you call Carpet as a Style and then the Carpet table can have Style, Colour and Size.

e.g.

Style
StyleID     StyleName   Description
1           'Modern'    'Striped'
2           'Rustic'    'Checks'

Carpet
CarpetID  StyleID  Colour           Size    Price
1         1        'Red-Green'      'Small' '£'
2         1        'Orange-Teal'    'Large' '£££'
3         2        'Violet-Magenta' 'Large' '£££££'

e.g. Design to answer "Tell me all the carpets that have xxx as a colour."

…
Carpet
CarpetID  StyleID  Size    Price
1         1        'Small' '$'
2         1        'Large' '$$$'
3         2        'Large' '$$$$$'

Colour
ColourID ColourName RGB
1        Red        255, 0, 0
2        Green      0, 255, 0

CarpetColour
CarpetID ColourID
1        1
1        2
2        3
2        4
...              
Comments