I'm hoping to scrounge some free crumbs of advice from you good people at dbforums:

I'm designing a ecommerce system for a customer.
They wish to add various products to the site.
A product may or may not have one or more options.
Any given option will have one or more values.
The value of an option may effect the price of a given product.

So for example they wish to add a duvay cover (they sell beds, bed linen, etc.)
They need to be able to add a colour option to this, and the colour option must be able to, for example, have the following values: blue, red, green, yellow, etc.
The duvay cover also needs to have a size option which will have the following values: small, double, king-size.
The colour option will not effect the price but the size option does.

I thought i could model this as follows:

product table: (this contains the products)
product_id, name, price
1, duvay cover, 50

option table: (this contains options that can be assigned to products)
option_id, option
1, colour
2, size

product_options table: (this links products to options)
product_id, option_id
1, 1
1, 2

option_values table: (this defines the values an option can have)
option_value_id, option_id, value
1, 1, blue
2, 1, red
3, 1, green
4, 1, yellow
5, 2, small
6, 2, double
7, 2, king-size

product_value_data table: (this defines what affect a selection of an option value has - at the moment just price adjustment)
product_id, option_value_id, price_adjust
1, 6, +15
1, 7, +20

so here choosing the double size adds 15 and choosing the king-size adds 20 to the standard price...

Any thoughts, criticisms, ideas, etc. would be very welcome....

Many Thanks

j

i would do it like this --

product table: (this contains the products)
product_id, name, price, colour, size
1, duvay cover, 50, blue, small
2, duvay cover, 65, blue, double
3, duvay cover, 70, blue, king-size
4, duvay cover, 50, red, small
5, duvay cover, 65, red, double
etc.

why make things harder than they have to be?

hi rudy, thanks for the reply..

- wont that lead to an awful lot of repetition? i mean the product name would be repeated for every possible combination of option values.

Further not all products have the same option choices so there would be a lot of null columns

Also adding a new option would mean, altering the table by adding new columns rather than just adding rows to an exiting table...

this depends entirely on your definition of awful

where do you think this repetition lies on a detrimental scale ranging from a negligible piffle on one end to having to explain late delivery to the client because application testing isn't finished at the other end?

in any case you might want to evaluate how this repetition will affect you

you can immediately see (i hope) the advantage of this simple design in terms of query construction -- no joins means easier queries

where does it hurt? perhaps in disk space

how many rows are there going to be? a few thousand? only if you expect a few billion should space matter

not possible combinations, only actual combinations, i.e. if you don't carry green double, then there's no row for that

again, define "a lot"

you didn't mention very many options besides colour and size, and i doubt a bedding store will have more than a dozen or two

in any case, ask yourself again how nulls would impact you

you are correct, but how often is this going to happen?

besides, you're having questions with just colours and sizes, imagine how many more tables you'll need and how much more complex your joins will be when these unmentioned other options have to be added to the mix

your choices: the bedding store adds another few options, you do a small update to the table and the application, versus the cost (in development time) to generalizing this using option tables

i know which way i'd go

Hi Rudy,

Thanks again for taking the time to reply.

Certainly I can see the advantage of the single table design you suggest, and there is certainly no concern regarding disk space.

The problems I forsee with a simple single table design are as follows:

1)
The same option may be used for various products, for example, the colour option is applicable to duvay covers, pillow covers, mattress covers, etc.

With the multi-table desing adding a colour to a new products, say cushion covers, will be as simple as inserting a record into the product_options table, with a single table design a record for each colour the product is available in would have to be added. - I'm just imaging all sorts of problems here - the person administering it types in the name or price wrong on one of the multiple records they would have to add.

Further, if a new colour is added, or a colour is removed or replaced this can simply be added, removed, or update in one place, the option_values table and all the products that have the colour option will be 'available' in the new colour range. With the simple table design whole records would have to be inserted, updated or remove for each product that had a colour.

2)
When displaying the products and the option choices with the simple table desing i imagine i'd have to check losts of query results to see if they came back null in order to decide whether to display them or not.

Any thoughts much welcome..

thanks

jim

this isn't a problem with the single table design

products where colour makes sense will have a value in the colour column, and products where it doesn't won't


this is where your application user interface comes into play -- to add a bunch of products which all have the same description, same price, same vendor, etc. etc., but only differ in colour, you would design the input form so that they enter the common stuff only once, and then check off the colours

again, be careful with this, you may find that by adding "chartreuse" to pillow covers also makes it "available" to products which actually don't come in that colour

no

there is only one table, so there is only one query, so you print the columns that have values, and you're done

hmmm... I see what you're saying, but those null fields seem wrong. - isn't it against one of the normal forms or something to have fields that are just not applicable to the item represented by the primary key in a row?

i know one good way for you to learn exactly how "wrong" they are

what i'd like you to try is to create the single table, write the single query needed to access it, and display the information on your web site, mindful of the NULLs which won't actually display

that really shouldn't take too long, agreed?

okay, next, i'd like you to go ahead and develop your version with all the option tables and option value tables and so on

after you're finished, which might take a fair bit longer (agreed?), i'd like you to come back and give your assessment of which approach was (a) harder, and (b) more aesthetically pleasing from a normal forms point of view

note: please answer (b) only after you've developed both versions

lol... ok, i will... might be a while before i post back...

thanks.

A caveat to Rudy's suggestion: it's much harder, once you have actual data in the system, to normalize than to denormalize.

That is to say, if you find you have integrity problems down the road, and want to solve them by normalizing, the denormalized data probably won't fit the new normalized model.

For example, Rudy says you can just leave color blank "when it's not needed." Sounds fine, but your data entry folks will *not* think the way you do nor will they think the way each other do, that's I can guarantee you. So these denormalized columns won't be used in a consistent manner.

If you never get beyond a few hundred items, it's not a problem. If you wind up with thousands of entries, it will be a major task to go through them and change the data and verify that you haven't changed the meaning.

That's if you *can* change the data. If it has to match invoices you've already sent to customers, you may be stuck with it...

I'm not saying everything has to be perfectly normalized, or that any design can anticipate every possible change. Just that a few hours of thought now, rather than doing the quick and dirty hack, might save you a lot of frustration down the road.

But, then, maybe that frustration will translate into a lot of billable hours. Maybe Rudy's on to something.

heh, that's cute, but no, i never give out counter-productive advice in the hope of snagging some consulting hours, that's not my game at all

in this instance i honestly believe the bedding business is not complicated enough to warrant option tables

as for users using the colour column when it's not applicable, if you do a good job on the user interface, they won't get that chance

Thanks for the further input, (just taking a break from trying to implement both versions) i just have a couple of quick queries:

1) Rudy, you mentioned a couple of times the importance of a good user interface in conjunction with the simple table design - am i not just making up for a badly designed data structure by using the user interface to control the users inputs?

2) Would you consider the option & option_values tables design i originally suggested to be a type of Entity-Attribute-Value model?

Thanks again

j

not really

if you do have a multi-table fully generalized database backend, do you somehow imagine that this will make your user interface simpler?

okay, assume the user interface is one simple form...

wouldn't that form begin to resemble the single table design?

also, if you don't do any user input editing in the application layer, what kind of error messages are you going to generate when you get a foreign key error on one of your option tables?



yes

yeah, i kind of do at the moment, (although i haven't finished implementing either design yet so i may be very wrong...)

ok, this is what i'm thinking (this is going to stray into the implementation so forgive me if its goes a bit off the Database Concepts & Design topic):

a user wants to add a new product.

I imagine the single-table design working as follows:

The user adds data that is going to be common to all optional variations of the product such as its name & description into a couple of text boxes.

They then have a series of textareas, one for each of the possible options where they can add a list of options, for example, there's one for colour options, one for size options etc.

In order to input this data into the database i'll have to explode the option values into arrays and loop through them inserting a line for all possible variations, so for example i'll have:

1, duvay cover, 50, blue, small
2, duvay cover, 65, blue, double
3, duvay cover, 70, blue, king-size
4, duvay cover, 50, red, small
5, duvay cover, 65, red, double
...etc..
Whereas:
I imagine the multi-table design working as follows:

The user adds data that is going to be common to all optional variations of the product such as its name & description into a couple of text boxes.

But then simply ticks or unticks a box to select which of the options (pulled from the options table) are applicable to the new product.

In order to input this data I insert the product data into the product table and insert the relevant data into the product-option table depending on what options have been selected.


That seems easier both for the end user/admin and from a coding point of view? no?


Many thanks again for taking the time to read & reply

j

now i want you to take a leap of faith

what would you need to do in order to present the user with checkboxes but assuming you were still targetting the new product to be stored into the single table?

i promise, this line of thought is actually leading somewhere