E-commerce data design issue

[daniyalnawaz]

Hi, I'm designing a database for an E-commerce web project. It is a tennis equipment shop contains products such as Racquets, Balls, Apparels and other sports accessories. Each product have different sets of option (E.g Racquets have strings, tension, size and Apparels have color, size).

First I was planning to make three tables for products:
"PRODUCT" -|----------|O< "OPTION_GROUP" -|---------|< "OPTION"

Each product will have an option group and the group further will contains options. But there some options that are same in the products (Some tennis racquets have same option list for selecting strings) and some options differ in the size in different products (One racquet is available in 3 sizes ,while other one in 2). So i've decided to make following schema.

"PRODUCT" -|----|O< "PRODUCT_OPTION" >O|---|- "OPTION_GROUP" -|---|< "OPTION"

"PRODUCT_OPTION" will be a junction table containing product,options_group and option IDs to create many-to-many relation among the three table. I've included options in it because it is not necessary that product will have same size of options than the option_group actually contains. So now there will be only one group and junction table will specify what option group product contains and how many options are in it.

So hows my plan?... Am I mistaking somewhere or is there another way to create more efficient and optimized database.

Thank You!
Dara Daniyal

[daniyalnawaz]

Another schema

[tesuji]

hi daniyalnawaz,

nice ERM. Maybe it s a good idea to state some rules about the cardinalities, for example:

A product can belong to more then as on product category
or (shall it be: a product can belong to one category only)

As for the product options:

Relationship between option_group and option is non identifying (dotted line) . If so, there is no need for an identifying relationship between product_option and option_group.

If an option belongs to different option_groups, then I would prefer identifying relationship between option_group and option (there, option would be weak entity). Further an identifying relationship between option and product_option. No relationship between option_group and product_option. (One should avoid loops in ERM because sometimes they cause trouble by duplicating foreign keys.)

btw, to be exact, in that ERM product_option is not an entity, it is a many-to-many relationship between option and product (well, I know, most graphical ERM design tools usually replace many-to-many relationships by a (joining) entity).

The solution would be slightly different if you use product category from your 2nd ERM to control the assignment of options to products.

krs,
tesu

[daniyalnawaz]

Thank you for reply tesu. Well i've created another schema. According to 2nd schema I want to include category because some options will differ according to the category of products (e.g. Raquets have different size measurement than Apparels).

Thank You...

[tesuji]

There is still a problem with entity option. Its pk is made of option_id and option_goup_id, what means that there would be a many-to-many relationship between option_group and a third thing like option_data. I think, option_group_id should be simple FK in option.
Look at these instances:

Category
cat_id     cat_name
----------------------
10          Tennis 
20          Scuba
30          Climbing  

Option_group
og_id    cat_id     og_name
---------------------------------
1	   10          Rackets           
2          10          Clothing 
3          30          Ice Axes

Impossible, therefore relationship (non-identifying) ok:
1           30         Rackets

Option
op_id     og_id     op_name
--------------------------------
1000      30        Size A
1001      30        Size B
1002      10        fibre strings
1003      10        natural strings

Possibly wrong, therefore relationship (identifying) not ok:
1000      30        fibre strings
1002      10        natural strings

Option group is ok, because relationship with category is non identifying. But option has some problems because same option_id can be combined arbitrarily with every option_group. I think an option can only be part of one option group, for example size measurements of rackets and apparels obviously differ in number and unit. Thus, it makes no sense defining a common size measurement option what can be assigned both option groups.

The attachment shows this simplification, maybe this would be enough (Also most modern ERP systems, e.g. MS Axapta, SAP R/3 possess classification systems where a specific product can only be part of one class/category. Then category_id would be non identifying part of product.).

krs,
tesu

[schumaj]

you will have to program in checks so that when a specific product_option is used for a type that is being used by a product the correct attributes will then become mandatory.

So lists all of you attributes in your PRODUCTS table and then join all three tables

And the reason for the 'may be' relationship, just because you have sold out of a product which was the last one to use that type doesn't mean you want to delete that type. so this will save you time.

This way is the most efficient once it is running and all types and options are selected.