Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Unanswered: Kinda Confused on Relationships for my database

    I am trying to add some things to my tshirt website

    I have a product that has one category, two images, more than one option(color choices), more than one size and the prices is based off the size. Here are the tables I have created but I know this isn't correct. Can someone help me put the together the correct way. I am really confused so any help will be really appreciated.

    Product
    ProductID
    prod_name
    prod_desc
    ImageID
    CategoryID
    OptionID
    PriceID
    SizeID

    Categories
    CategoryID
    Category

    ImageID
    ImageID
    image
    th_image

    Options
    OptionID
    options

    Price
    PriceID
    Price

    Size
    SizeID
    Size
    PriceID

  2. #2
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    I'd suggest the following:

    PRODUCT table
    product_id
    category_id
    prod_name
    prod_desc
    large_image
    thumbnail_image
    (show/hide indicator)

    notes:
    - the thumbnail and the large image name would be held for each product (assuming you are going to store the image filenames and reference them from an image directory instead of trying to store them in the database
    - a show/hide indicator would be useful to take products off the shelf so to speak without deleting them from the PRODUCT table
    - I have moved the option (colour), price and size fields into the new ORDER_DETAIL table below.

    CATEGORY table
    category_id
    category

    OPTION table
    option_id
    option

    notes:
    - used for tshirt colour

    SIZE table
    size_id
    size
    price_id

    notes:
    - I have assumed that the price of a tshirt depends on the size, and that all tshirts are the same price... this approach would not be appropriate if some tshirts are more expensive than others...for example a more complete solution would be to hold a base price in the PRODUCT table for each tshirt which would enable some tshirts to be more expensive than others and then use the SIZE/PRICE tables to either apply discounts to smaller tshirts or increase the price of larger tshirts

    PRICE table
    price_id
    price

    ORDER table
    order_id
    order_datetime
    customer_id
    price_grand_total
    date_payment_received
    date_goods_despatched

    notes:
    - the order table is important since it would capture customer transactions

    ORDER_DETAIL table
    order_detail_id
    order_id
    product_id
    option_id
    size_id
    quantity
    price_sub_total

    notes:
    - an ORDER_DETAIL table is of use if a customer can purchase more than one type of tshirt or more than one of the same kind of tshirt
    - it is useful to store the price with the other transaction information to 1) show sales history, and 2) enable you to change prices at any time without affecting historic sales transactions.
    - always record the date/time when orders are placed since a small percentage of customers are bound ask questions about their orders

    CUSTOMER table
    customer_id
    customer_first_name
    customer_last_name
    customer_email
    customer_address_line1
    (etc.)

    notes:
    - this CUSTOMER table enables you to hold their address & contact details in one place.
    - this would also be of use in terms of sending occassional promotion offers to past customers

Posting Permissions

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