Results 1 to 9 of 9
  1. #1
    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. no

    2. how many items can a customer order at one time?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,424
    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 on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    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

    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.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,424
    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 on the Tiger 800 or the Norton

  8. #8
    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

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,424
    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 on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •