If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Problems arise when designing database for ecommerce

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 4
Smile Problems arise when designing database for ecommerce

Hi guys, first of all i dont know if it appropriate to post such topic.. But please help me out.
So im a newbie in database design, and i want to have a practice.
Then im thinking about practicing to make fashion online shop website(it will sell shoes, t-shirt, shirt, accesories, etc)

So first of all i design my database for customers, products, and orders, i come up with 5 tables:

1. customers
  • customer_id
  • email
  • password
  • first_name
  • last_name
  • registration_date

2. products
  • product_id
  • type_id (refers to type table)
  • price

3. type
  • type_id
  • name
  • description

4. order_log
  • order_log_id
  • order_description_id (refers to order_description table)
  • customer_id (refers to customer table)

5. order_description
  • order_description_id
  • product_id (refers to product_id)
  • quantity
  • price
  • date

so after seeing my own design, im thinkin "Oh my God, for type like shoes/t-shirt/shirt they will have a size!, but accesories dont" the questions are:

1. should i add 1 more table like product_description? so in the product table, there will be product_description_id. If the product_description_id is 0, then it doesnt have any more description like size, but if its not 0 then it has..

2. i find that my database design a bit ermm awkawrd.. can u guys give some tips to optimize it?

thx for reading the long post! i really appreciate it, i dont have any computer backgrounds nor computer education, im an accountant student whose wanted to learn web + database, please do support
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
1. no

2. how many items can a customer order at one time?
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 4
Quote:
Originally Posted by r937 View Post
1. no

2. how many items can a customer order at one time?
so what im thinking is maybe ill ad size in the product table, so size 0 for accesories(means fits for all) and for others with size i can give them a value..

hmm i think a customer can order unlimited as long as there is stock available
Reply With Quote
  #4 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,665
do you intend storing a separate product for each size/colour or product.
bear in mind a shoe could have multiple sizes
a tee short could have multiple sizes and multiple colours but notionally be the same prodcut (IE has the same product description).
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #5 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
Quote:
Originally Posted by mohur View Post
hmm i think a customer can order unlimited as long as there is stock available
your design appears to limit the order_log to only one order_description
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 4
Quote:
Originally Posted by healdem View Post
do you intend storing a separate product for each size/colour or product.
bear in mind a shoe could have multiple sizes
a tee short could have multiple sizes and multiple colours but notionally be the same prodcut (IE has the same product description).
i think i can give it an ENUM for each size / colour

Quote:
your design appears to limit the order_log to only one order_description
aww man, thx! U point out really clear! Thats the problem now! haha
so, what should i do now ? im a bit confused again this problem :s

edited:
so, here's my new table, i dont know if this will fit iin the situation =
**1. customers**

* customer_id
* email
* password
* first_name
* last_name
* registration_date

**2. products**

* product_id
* type_id (refers to type table)
* price
* size

**3. Type**

* type_id
* name
* description


**4. order**

* order_id
* customer_id (refers to customer table)
* total_price
* total_tax
* date


**5. order_item**

* order_item_id
* order_id
* product_id (refers to product_id)
* quantity
* price

if customer bought more than 1 item , then i will store each product(type) in the order_item, and order_id in the order_item will refer to the same order_id. how about that? will it fit the situation? sry if im asking too much

Last edited by mohur; 01-05-13 at 00:13.
Reply With Quote
  #7 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,665
Quote:
Originally Posted by mohur View Post
i think i can give it an ENUM for each size / colour
really?
does that mean you intend having a separate product table (or perhaps got down the super/sub type and separate sub table) for each type or product
sizing isn't common in clothing let alone clothing and shoes

ferisntance
womens clothign is sized according to some psuedo random number system running form 00 to what ever, mens clothing is sized according to some other metric (could be waist + trouser leg, could be chest size + collar size, or it could be a letter code [XS,S,M,L...XXXXL] another thing to bear in mind is that sizes can be different and very from country to country.

there is a world of difference between a product and an instance of that product. ferisntace you may have a printed tee shirt
almost certainly you'd have separate products for Men, Women & children.
hanging off that in intersection tables I'd expect there to be further definitions of say size, and colour
for shirts/blouses/tops like wise
except that you may need collar and chest sizes for men and so on. an enumeration isnt' goijng to handle that level of complexity
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 4
Quote:
Originally Posted by healdem View Post
really?
does that mean you intend having a separate product table (or perhaps got down the super/sub type and separate sub table) for each type or product
sizing isn't common in clothing let alone clothing and shoes

ferisntance
womens clothign is sized according to some psuedo random number system running form 00 to what ever, mens clothing is sized according to some other metric (could be waist + trouser leg, could be chest size + collar size, or it could be a letter code [XS,S,M,L...XXXXL] another thing to bear in mind is that sizes can be different and very from country to country.

there is a world of difference between a product and an instance of that product. ferisntace you may have a printed tee shirt
almost certainly you'd have separate products for Men, Women & children.
hanging off that in intersection tables I'd expect there to be further definitions of say size, and colour
for shirts/blouses/tops like wise
except that you may need collar and chest sizes for men and so on. an enumeration isnt' goijng to handle that level of complexity
wowww, u pointed a lot of problems that i didnt think about them before.
i wasnt thinking about those problems when designing those.
can u help me design it? what table should i add/remove? well this is my first database design that coming from my own(before i just practicing from php book) , so i got like zero experience. i really appreciate u pointed out that problem
Reply With Quote
  #9 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,665
if you are learning, then Im not being a smart aleck, you will learn more from the process if you develop the design yourself with your business rules in mind. people here will help but it needs to be from you as only you truly understand what you need
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On