•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Database Design section within the Web Development category of DaniWeb, a massive community of 375,231 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,195 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Database Design advertiser:
Views: 509 | Replies: 5
 |
•
•
Join Date: May 2008
Posts: 7
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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
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
•
•
Join Date: May 2008
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Another schema
•
•
Join Date: Apr 2008
Posts: 290
Reputation:
Rep Power: 1
Solved Threads: 39
Posting Whiz in Training
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
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
•
•
Join Date: May 2008
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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...
Thank You...
•
•
Join Date: Apr 2008
Posts: 290
Reputation:
Rep Power: 1
Solved Threads: 39
Posting Whiz in Training
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:
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
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
Last edited by tesuji : May 13th, 2008 at 1:04 pm.
•
•
Join Date: Apr 2008
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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.
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.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb Database Design Marketplace
Linear Mode
•
•
•
•
access advice banking breach business code combo commerce creative daniweb data data protection database design drive dropdownlist encryption europe finance forensic forensics google hacking hardware help howto hp illustrator industrial espionage internet it module money navigation net news photoshop print privacy protection research reuse search security storage survey toread tutorials web wikipedia
- memory management in wndows 2000 (Windows NT / 2000 / XP / 2003)
- Timming Role in Exchanging Tranditional Commerce to E-commerce (eCommerce)
Other Threads in the Database Design Forum
- Previous Thread: Beauty Listings Design - Database?
- Next Thread: Email /Messaging System Table design
