Search code examples
mysql

How can I create the database table for shopping website


I am little bit confused that how I have to create the tables for simple shopping website because I am new in php and this is my first project of shopping website choosen by myself for practice but I am confused with the size of the products and color of the products and different types of the products like laptops, mobile, jeans, shirts etc,

I have created the table but I want to know that two things

  1. for electronics products I have to create a different table and for

  2. clothes like jeans and shirts etc, I have to create a different table

    Please can anybody tell me the structure of the table.


Solution

  • You'll have many tables, for example:

    Products:
        ID
        Name
        Description
        Etc...
    
    Categories:
        ID
        Name
    
    ProductCategories:
        ProductID
        CategoryID
    
    Types:
        ID
        Name
    
    ProductTypes:
        ProductID
        TypeID
    

    This enables you to have products in multiple categories and of multiple types...

    Added further examples as per the comment below. For sizes you have a Sizes table, and a mapping table to map products to sizes...

    Sizes:
        ID
        Name
    
    ProductSizes:
        ProductID
        SizeID
    

    If you also need to consider colours, and need to make a choice whether you will store sizes that have a colour, or colours that have a size...

    Colours:
        ID
        Name
    
    ProductColours
        ProductID
        ColourID
    

    Or, if you need colours and sizes, or any further complexity, i would abstract it right out and use a ProductAttributes way of doing it, maybe out of the scope of this question as the answer would be quite lengthy indeed.

    But in the same style as above, you could have a table like so:

    ProductSizesColours:
        ProductID
        SizeID
        ColourID
    

    Which enables you to store data for products such as:

    A pair of jeans that are size 32 and colour brown. A juice blenderthat is 3 litre size and colour white. Etc...