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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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