locked
Product Catalog database RRS feed

  • Question

  • Hi,

    I am creating a database for an online catalog of products. The database orders when submitted will merely generate an email with the order in, and store what was requested. I just need a little advice for the best way to set up the product / size tables and relationships.

    The items are mainly clothing with various size options. I don't need to manage the stock levels, as the orders are checked by a member of staff before then being ordered from another company. The purpose of the database is merely to allow people to put in a request for an order, so these can be processed later.

    Would it make sense to structure the tables as below?

    PRODUCT: Product_Id (PK), Product_Name, Product_Description
    SIZE: Size_Name (PK), Size_Measurements
    PRODUCT_SIZE: Product_Id (PFK), Size_Name (PFK)

    Tuesday, June 18, 2013 2:46 PM

Answers

  • Your approach looks like the best normalized approach, what about colours though?

    When I did this last for a clothing company I ended up with four tables

    Product (Product_ID,.....)

    Colour (Colour_ID, Coulour_Desc...) & Size (Size_ID, Size_Desc....)

    Product_Colour_Size (Product_ID,Colour_ID,Size_ID, Price.....)


    "Everything in the world should be made as simple as possible, but NOT simpler" A.Einstein

    Tuesday, June 18, 2013 4:59 PM

All replies

  • Your approach looks like the best normalized approach, what about colours though?

    When I did this last for a clothing company I ended up with four tables

    Product (Product_ID,.....)

    Colour (Colour_ID, Coulour_Desc...) & Size (Size_ID, Size_Desc....)

    Product_Colour_Size (Product_ID,Colour_ID,Size_ID, Price.....)


    "Everything in the world should be made as simple as possible, but NOT simpler" A.Einstein

    Tuesday, June 18, 2013 4:59 PM
  • Don't you need to know the Suppliers of the Product items also? This would help your staff to process the orders.

    Perhaps, you need to introduce the Entity [Supplier] into your database and decide whether you need to have  the Relationship between [Supplier] and [Product] or [Supplier] and [Product_Size] and whether this Relationship is 1-to-Many or Many-to-Many.

     


    Van Dinh

    Wednesday, June 19, 2013 12:40 AM
  • I think the way you are thinking is the best as way to go, since the database is merely to allow people to put in a request for an order with clothing product and the size, however if you are thinking about adding features like color or Manufacturer of the clothing, the you can do as GWilly has mentioned. 
    Wednesday, June 19, 2013 12:52 AM
  • I don't need to know things like supplier etc. Granted this is a strange one, but I literally just need a form that purchasing staff can put products on, and employees can then place an "order request" not an actual order.

    The purchasing staff then look at each "order request" individually and decide whether the order is allowed. If it is, then they go to the supplier and place an actual order.

    Wednesday, June 19, 2013 3:17 PM