Hi. I need a good database design for a product catalog.
I need this to store pricing information. When a customer order a product, I have to find the price of that product according to the options chosen.
Each of my products can have options... and each of those options can have options.. etc.
Below you can see a part of the options structure.
First I was thinking I can give each option a price and then calculate the total. But I can not use that approach.
It is actually not me that decides the prices, I get them from a third party.
They could of cause give me a price for each option.
But... Lets say
binding -> side -> top = 1 dollar.
binding -> type -> coil binding = 1 dollar.
So if a user chooses to have those two options the price = 2 dollars...
But, it could be that the production of these two options together would be more costly cause of some circumstances.
binding -> side -> top + binding -> type -> coil binding = 3 dollar.
I could create one table with all combinations, and have a total price for each of those combinations. What you see below is only a part of the options, there are many more. So this table would contain many columns, and that would also kind of be impossible cause the number of records.
Some of the options will also exclude each other, but that is not up to my system to handle that.
I guess I am not the first with a problem like this.
I use Postgres 9.2 and Java.
edge gluing (padded)
soft cover (perfect binding)
tape (inexpensive version of the soft cover)
I have a number of Product-related Data Models on my Database Answers Web Site.
This looks like the most suitable for you because it combines a flexible product structure with a Price for a Product :- Complex Product Catalogs Data Model