SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Portland
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question E-Commerce Products & Product Options (SQL)

    Hello All!

    I am not a newbie to ASP.net, but am fairly inexperienced with database design. I have designed databases in the past using SQL, but am drawing a blank when it comes to this. SQL doesn't really matter here.. This is really more of a database design question.

    I am needing to develop a database for an e-commerce site. This e-commerce site will be selling various items such as clothing lines, lotions, soaps, etc...

    Obviously each product will need the basic info such as price, sku#, reg price, sale price, images, etc... That is easy.

    I also need options for each item. Each item will vary. Some will be clothing size options (S, M, X, XL)... weight (4oz, 6oz, 16oz), color, scent, etc... Ideally I want a back end portal for someone to use to add, delete, update the items into the db. Therefore, as dynamic as possible is ideal for the options.

    I was thinking of having a category table, subcategory table (?), products table, product_options table, and ???

    Anyone have any experience with this? Will be happy to contact you via email/phone as well if necessary. Thanks in advance!

    Allan
    29thlevel@gmail.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    do you have a specific question that we can discuss on the forum?

    if you wish to contact someone for private consulting, perhaps you should post that request in the Looking to Hire forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Portland
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    Sorry, I should have myself a little clearer.

    I am seeking advice for a database that I am designing. I have designed shopping carts in the past but only for products that didn't require options or any sort of variables. Now I am needing to set up a db full of products with multiple variables. Just looking for advice on how the best way to proceed is in regards to building the tables.

    Products table
    Product options table
    Do I need a 3rd or 4th table?

    Some products may have no options, while others might have 1, 2, or 3 options.

    As an example (as this is not completely detailed) I would have:

    Products
    _________
    ProductId
    ProductName
    Description
    Sku
    ItemNo
    RegPrice
    Image1
    Image2
    etc...

    ProductOptions
    _________
    OptionID
    Product Id
    Option
    Description

    I'm definately missing something here. Keep in mind that some products will have no options, while others could have up to 4. Some might simply be clothing sizes, others could be colors, others could be weight, etc...

    Can anyone lead me in the right direction as to how my tables should look?

    Thanks!
    Last edited by 29thlevel; Jan 16, 2005 at 12:54. Reason: make it clearer

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    so far so good

    that is the best way to design multiple options -- in a separate table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in the ProductOptions table, you want to remove the Option and Description columns and put them in their own table, Options

    Products
    1001 widget
    1002 shirt
    1003 gizmo

    Options
    1 small
    2 medium
    3 large
    4 onesizefitsall

    ProductOptions
    1002 2
    1002 3
    1004 4


    so you can see that widget has no options, shirt is available in medium and large but not small, and gizmo is available only in onesizefitsall
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Location
    Hailsham, UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I am looking at doing a similar thing.

    The bit I'm having trouble figuring out for the database is what I need to do to allow combinations of valid product options to affect a price. There can be more than one set of options.

    For example, one piece of data to be stored might be:
    For product t-shirt : small + black + longsleeved = 20.00

    It's this relationship I'm not sure how to structure.

    Any ideas, URLs, other threads or things to search Google on?
    Thanks, Ian


Bookmarks

Posting Permissions

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