Whats the best way to design a database with a product of multiple variations?

Question

This question is related to this one: Best way to store product colors in a database

So i have a Product which have the multiple properties. Below is an illustration.

Product A

Name: Product A v1.0, Product A v2.0 (2 versions of Product A)
Color: Black, White, Blue
Capacity: 16MB, 32MB, 64MB
Carrier: T-Mobile, Digicel, Vonage

This product can only have one of each property. Ex: a Black 32MB, Vonage product A v2.0

I am unsure of how to setup this product in a normalized database. Do I create separate tables for Product Name, Color, Capacity and Carrier.. and then create a linking table between Product A v1.0 and Color, Product A v1.0 and Capacity, Product A v1.0 and Carrier and so on?

I am also unsure because, Product A v1.0 only offers 16MB Black/White, while v2.0 offers all colors, all capacities and only one cellular carrier. And i could go on with other minor variations.

Must it have one of each property? Could you have a product without a carrier for instance? — corsiKa, Mar 23 '12 at 21:46
Why not have a column for each, and a value in there. Name is probably a varchar, color you could just store the color as text (or have a separate table that maps to an int - probably overkill imo), capacity store as an int, and carrier probably use a CarrierId that links to another table of Carriers (so you can add more about a carrier) — Prescott, Mar 23 '12 at 21:48
@corsiKa no, Product A v1.0 for instance does not support vonage carrier. — Just a coder, Mar 23 '12 at 21:57
@Prescott you mean have a column for each like this? ------------------- name|color|carrier|capacity| etc? — Just a coder, Mar 23 '12 at 22:01
Yeah, why not? That's 4 columns, maybe 5 if you add a surrogate primary key (like an auto incremented int). If they only have one value for each, then that will be fine. If they could have multiple values (like Product A could be on carrier T-Mobile and Digicel), they you want to talk about another table similar to Dan's answer — Prescott, Mar 23 '12 at 22:16
@Prescott ok maybe i confused you a bit. A customer can choose Product A with T-mobile and another customer can also chose Product A with Digicel. So i'm guessing i'll look into Dan's answer some more. — Just a coder, Mar 23 '12 at 22:24

Dan A. · Accepted Answer · 2012-03-23T22:43:13.110

6

Here's an example of how I would link product and colors in Oracle (updated to show how you can create a primary key on products with a separate unique key):

CREATE TABLE product_color (
    color_name VARCHAR2(100),
    CONSTRAINT color_pk PRIMARY KEY (color_name)
);

CREATE TABLE product (
    product_id NUMBER,
    product_name VARCHAR2(100),
    color_name VARCHAR2(100),
    CONSTRAINT product_pk PRIMARY KEY (product_id),
    CONSTRAINT color_fk FOREIGN KEY (color_name) 
        REFERENCES product_color (color_name),
    CONSTRAINT product_uk UNIQUE (product_name, color_name)
);

This basically creates a foreign key to a value table. If you try to insert a color or invalid text not in the product_color table, the constraint will prevent it.

Now, I could have normalized this further and associated an integer key with each color, and created a foreign key to the integer. However, this type of normalization will lead to a lot of joins in your SQL statements, particularly when you start adding additional attributes. I find this way to be a good compromise with cleaner SQL.

edited Mar 23 '12 at 22:43

answered Mar 23 '12 at 22:12

Dan A.

2,914
18
23

+1 for saying don't use surrogate keys where they're not needed. – Ben Mar 23 '12 at 22:24
@Ben, I've maintained applications that go completely overboard with the surrogate keys. After trying to maintain SQL statements with 20 or 30 joins, I've learned to appreciate the beauty of simplicity. :) – Dan A. Mar 23 '12 at 22:27
Ok I understand, I can create a Product table, and then add Color, capacity, and carrier to the table as foreign keys. So the table will end up having: product_name(pk), color(fk), capacity(fk), carrier(fk). Sounds good BUT, if the product_name is the primary key, how can i have the following 2 variations in the Product table? : (1) productA, 16MB, Black, T-mobile ** (2) productA, 32MB, Black, T-mobile.. ? – Just a coder Mar 23 '12 at 22:31
@user76859403 You can create a composite primary key on all of the fields that uniquely identify a product in your table. So maybe PRIMARY KEY (product_name, color, capacity, carrier). – Dan A. Mar 23 '12 at 22:34
On the other hand, it may be easier to create an auto-incrementing primary key so you have an easier way to pull a product from the database without having to pass in multiple fields. Then, to prevent duplicate products, create a separate composite UNIQUE key on all of those fields. – Dan A. Mar 23 '12 at 22:36
You're welcome! I've updated my answer to show you an example of a primary key with a separate unique key. – Dan A. Mar 23 '12 at 22:43
the only issue about this response is: i will have many variation primary keys... like.. very many. I only showed you guys color, capacity, and carrier.. but there are many other varying factors that will result in many primary keys. I wish there was a simpler way. – Just a coder Mar 23 '12 at 23:25
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/9248/discussion-between-dan-a-and-user76859403) – Dan A. Mar 23 '12 at 23:29

Whats the best way to design a database with a product of multiple variations?

1 Answers1