September 16, 2013 at 8:36 am
Hi All,
I need to create an attribute table for say a product. Each of the attributes for a product can have have different fixed 'look-up' types.
My question is do I create a look-up table for each type or enter the look-up value straight into the table. The types are fixed and will rarely ever change.
I will probably have about 2000 - 3000 rows in the the table
e.g
Product = T-shirt
attribute 1 - colour : red/blue/pink/green/black/white
attribute 2 - size : XS/S/M/L/XL
attribute 3 - cut : slim/standard/taylor
So do have this all stored in the product_attributes table:
Product ID - 1
Colour - red
size- M
cut - standard
Or use look up tables with ids
Product ID - 1
Colour - 1 - foreign key to product_colour table
size- 3 - foreign key to product_size table
cut - 2 - foreign key to product_cut table
I want to do look up tables as i feel that it is best practice, but for querying the data it would be simpler to have the data straight in the table.
Also there wont be million rows (max 10,000) in the Table so searching the data should still remain relatively quick.
Many thanks
D
September 16, 2013 at 8:44 am
bugg (9/16/2013)
Hi All,I need to create an attribute table for say a product. Each of the attributes for a product can have have different fixed 'look-up' types.
My question is do I create a look-up table for each type or enter the look-up value straight into the table. The types are fixed and will rarely ever change.
I will probably have about 2000 - 3000 rows in the the table
e.g
Product = T-shirt
attribute 1 - colour : red/blue/pink/green/black/white
attribute 2 - size : XS/S/M/L/XL
attribute 3 - cut : slim/standard/taylor
So do have this all stored in the product_attributes table:
Product ID - 1
Colour - red
size- M
cut - standard
Or use look up tables with ids
Product ID - 1
Colour - 1 - foreign key to product_colour table
size- 3 - foreign key to product_size table
cut - 2 - foreign key to product_cut table
I want to do look up tables as i feel that it is best practice, but for querying the data it would be simpler to have the data straight in the table.
Also there wont be million rows (max 10,000) in the Table so searching the data should still remain relatively quick.
Many thanks
D
Go with the lookup tables. It will maintain domain integrity, and make things simpler if you need to rename an attribute.
If you need to make queries simpler, create a view with the product table joined to the lookup tables. And don't forget to put indexes on the FK columns in the product table.
September 16, 2013 at 8:51 am
Yeah, I'd absolutely go with the tables too. It's the single easiest, most direct, most efficient way to maintain data integrity. It's the very reason you're using a relational engine and not an ID/Value storage engine.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 16, 2013 at 9:06 am
Awesome guys thanks the responses, just needed some confirmation that was the way to go , cheers
D
September 16, 2013 at 10:49 am
Just as a possibility: Check constraints instead will reduce overhead slightly, since another table doesn't have to accessed. For a very limited number of known and easily recognized values, for example "gender_code" ("F"/"M") -- or sizes?? ("S","M","L","XL") --, the overhead of an additional table may seem excessive.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 17, 2013 at 1:28 am
ScottPletcher (9/16/2013)
Just as a possibility: Check constraints instead will reduce overhead slightly, since another table doesn't have to accessed. For a very limited number of known and easily recognized values, for example "gender_code" ("F"/"M") -- or sizes?? ("S","M","L","XL") --, the overhead of an additional table may seem excessive.
Thanks Scott, the values aren't as simple as those that was just an example many thanks though .
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply