0

This is frying my brain, I really need help! Here is the thing i want to achieve.

I have a Table name Product. The product may or may not have up to two Optional field. Example Color and Size.

If the product does not have the optional field, it will have only one row of Price and Quantity, else for each row of optional field, there will be one price and Quantity.

I know this sound Confusing, pardon me. I'm confused too. ): But i can give you guys fews example below.

So the one million dollar question is, what are the tables and it's field i should create?

[ Product Without Optional Field ]

Price | Quantity

$1.00 | 2

[ Product With One Optional Field ]

Price | Quantity | Size

$1.00 | 2 | Large

$2.00 | 1 | Small

[ Product With Two Optional Field ]

Price | Quantity | Size | Color

$1.00 | 2 | Large | Green

$2.00 | 1 | Small | Blue

I come up with an idea of having Two entity named Product and Optional to have many to many relationship with the Optional Entity to store the field name, example Size and the junction-entity name Product_Optional will store the value, example Large.

However I'm still stuck with the issue of how to bind the Two Optional Field of one product to the same price and quantity! SORRY to be confusing :(

Sydnal
  • 21
  • 2
  • Can you tell us more about the optional field? Why is it limited to a maximum of two items, and is there a limit to what those items may be (color, weight, size, etc.)? – Shane Delmore Mar 08 '11 at 20:19
  • Well, two is the limit i want to enforce on so it's less confusing. And those optional field could be anything. The examples i used are Size and color, it could be Height and Width of the product instead. Thanks for the reply! – Sydnal Mar 08 '11 at 20:28
  • You are trying very hard to defeat the exact functionality relational databases were created to provide. You have a one-to-many (a few of them, actually) relationship and you are trying to stuff it all into one table. That is not less confusing. The table layout in my response below is the approach you should be taking. – David Mar 08 '11 at 20:37
  • @David. I completely disagree. Building an EAV table is also a complete repudiation of normal forms. Your ProductOptions table is an anti-pattern, an example of what not to do. http://stackoverflow.com/questions/4066463/should-i-use-eav-model/4066568#4066568 – Stephanie Page Mar 08 '11 at 22:33
  • @Stephanie - feel free to disagree, but don't present your opinion as if it were fact. Even the link you posted was simply an articulation of the pros / cons of that approach. It's still widely used and valid in many cases. Ultimately, the real world is not a university course on relational theory; there are many instances where it computationally preferable NOT to normalize. – David Mar 09 '11 at 15:44
  • @David, I agree that denormalizing is appropriate, I have seen far too many systems built on EAV's become complete nightmares. While you may have some experience in database design and can choose to suffer from an EAV, most people who ask questions here are novices and recommending a fraught design is a disservice. If you knew me, I'm anything but a 5NF Nazi. However shoving an EAV into an RDBMS is something I'll battle whenever I find it. If an EAV is the best schema then also recommend a switch to a database that is built for that. – Stephanie Page Mar 09 '11 at 15:58
  • Fair enough. I'll agree that it can get complex if you don't have a good handle on your requirements up front. I'm curious to know what your approach would be. – David Mar 09 '11 at 16:35

2 Answers2

1

Edit:

Since your options are unknown, you could do something like this

Products

id
product_name
product_description
...

ProductOptions

id
option (size, color, whatever)
value (large, blue, anything)

ProductInventory

id
product_id
product_option_id
quantity
price

Then your records in ProductInventory would look like:

1 | 1 | 1 | 5 | 2.00
1 | 1 | 2 | 3 | 3.00

etc etc

More detailed example, using the table structure above:

Products

1 | Product 1 | Prod 1 Description
2 | Product 2 | Prod 2 Description
3 | Product 3 | Prod 3 Description

ProductOptions

1 | Size | Small
2 | Size | Medium
3 | Size | Large
4 | Color | Blue
5 | Color | Red
6 | Color | Green
7 | Width | 10 Inches
8 | ... (as many as you want)

ProductInventory

1 | 1 | 1 | 5 | 2.00
(says for product 1, size small, there are 5 quantity, and cost is 2.00

2 | 1 | 2 | 17 | 3.00
(says for product 1, size medium, there are 17 quantity, and cost is 3.00

etc

David
  • 2,550
  • 7
  • 27
  • 29
  • Thanks for the reply! Well, the issue is that I don't want to define the optional field name, meaning ProductSize could be ProductWidth or anything. :\ – Sydnal Mar 08 '11 at 20:25
  • You could have Optional1 and Optional2 as fields in your Products table, and put anything you want in them I suppose. I don't really understand your problem then? – David Mar 08 '11 at 20:29
  • Yup, I tot about that too. But for different value of optional1 and optional2 there may be a different in Price and Quantity. Example, Size Large with Color Blue May have the price of 3 dollar while Size Small with Color Red may have the price of 2 dollar instead. Thk for the reply (: – Sydnal Mar 08 '11 at 20:34
  • That is exactly why you need multiple tables. You need one table for Products, and another table for each variation of a Product. So you could have productID=1, size=small, price=2.00; productID=1, size=large, price=3.00; etc. – David Mar 08 '11 at 20:39
  • Thanks for the reply! (: However my application will be adding the variation of Product in runtime. So i may end up with a lot of table! Size,width,height,Color,Weight etc. Will it be a fine approach? :\ – Sydnal Mar 08 '11 at 20:47
  • My edited approach is the way to go, since you have an unknown number of options. You can add as many options as you like at run time, and the relationship defined above will hold. – David Mar 08 '11 at 20:48
0

I have run into the same problem, and I think you need more than one table as well. try this:

products

id
product_name
product_price
.....

Product_options 

id
product_option_name

product_options_values

link your product_options_values to your product_options using product_options_id as foriegn key. example: product_option 'size' has the product_option_values of 'small', 'medium', 'large'*

id
product_options_id (fk)
options_value
options_value_price

product_options_to_products

this table links your products to your options. here you'd assign different options to different products.

id
product_id
product_options_id
E.E.33
  • 2,013
  • 3
  • 22
  • 34