Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Unanswered: Question regarding T-shirt Database design.

    Hi there.

    This forum seems to be a really great place to be. This is my first post on this forum

    Now I am creating an e-commerce website, in which the Supplier will have the option when listing his product i.e. a T-shirt listing.

    He will have the option for specifying the QTY of a particular T-shirt available in size and color in his stock. For Example:

    T-Shirt Variation : Qty
    Small - RED : 8
    Medium - RED : 5
    Large - RED : 7

    Small - BLUE : 4
    Medium - BLUE : 6
    Large - BLUE : 2

    Small - GREEN : 5
    Medium - GREEN : 3
    Large - GREEN : 6

    Now I have a Product table that has the product details and I would like to know how I can design this table to hold these values.

    Shall I create a Product table and then a Product_Variation table? and have some Foreign Key relationship? maybe One-to-One or Many-to-Many?

    Kindly advice how I can design my database to hold this data.

    Any comment/feedback is always welcome!

    Best regards.

    Thank you!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This is a common basic design, which is resolved differently by many. Its foundation lyes in bill of materials principal. In your case I would have a product master table, surrounded by sizes master and colors master. The fourth table will have ProductID, SizeID, ColorID, Qty, UnitCost, and UnitPrice. This is the "skeleton" of the design, and needs a lot more, like spllier master (with supplier ID present in Product Master), price change history, etc.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Aug 2010
    Posts
    2

    is this what we are looking for?

    Hi rdjabarov,

    Nice to hear from you. As you mentioned, I've created 4 tables in the database i.e.

    1. ProductMaster
    2. Product_SizeColor
    3. SizeMaster
    4. ColorMaster


    Please see in the attached Screetshot of how I have created and did the relationship among the tables.

    There is also a web page Screenshot of what we're trying to accomplish in the Supplier product admin page. The screenshot is attached..

    Please let me know if the tables structure looks ok and what more can we do to improve.

    Thank you!

    See 2 attachments:
    1 - t-shirt Db Diagram.png
    2 - t-shirt Supplier admin page.png
    Attached Thumbnails Attached Thumbnails t-shirt Db Diagram.png   t-shirt Supplier admin page.png  

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to remove SizeID and ColorID from PRODUCT table. And I think you're missing Supplier master table, and consequently the presence of SupplierID in PRODUCT.

    The form from the screen shot limits the ability to add more combinations of color/size. It is better to have it displayed as a grid, with Add/Edit/Delete buttons, that allow you to enter as many combinations of size/color as the supplier has to offer.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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