Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    6

    Unanswered: Help with designing an online shop

    Hello all,

    I need to design a database for a shop that sells many products from different categories.
    For example :

    cameras - that have the characteristics of:
    ISO speed, Zoom, resolution and many unique characters for cameras.

    fridges - that have that have the characteristics of:
    number of doors, volume, width , color and etc.

    how can i build this database if each type of products have it's own characteristics ?

    should i build a table for each type ? like cameras table, fridges table?
    or i can have one table for all the products? but than how can i make difference with each unique characteristics ?

    thanks a lot,
    Amit

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Please refer
    https://support.ca.com/cadocs/0/CA%2...tm?254775.html

    Similar to the example given in the URL, cant we think of a subtype relation in this scenario? say Product (master table with the common attributes) and one table for each distinct category - camera/fridge/TV etc..


    Product
    --------
    ProductID Name Manufacturer Type
    1 P1 Canon Camera
    2 P2 IFB Fridge


    Camera
    --------
    ProductID ISO Speed Zoom Resolution
    1 --- ---- -----


    Fridge
    -------
    ProductID Doors Volume Color Width
    2 ---- ---- --- ----
    Cheers....

    baburajv

  3. #3
    Join Date
    Nov 2011
    Posts
    6

    another question

    thank you,

    i have another question.

    for the same example.
    lest say for the ISO Speed i have certain values like:
    50 , 200 , 300 , 80 ...
    and for the Megapixels:
    1.34 Megapixels, 10.37 ....

    so i need to have a table with possible values for each attribute?
    ISO Speed table, Resolution table?

    Megapixels Table
    ----------------
    1.34
    10.37
    .
    .
    .

    or should i have a one big table like this:

    AtributeName | Value
    --------------------
    Megapixels 1.34
    Megapixels 10.37
    Megapixels 12.4
    .
    .
    Resolution 50
    Resolution 200
    Resolution 300

    thank you

  4. #4
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    This way, the searches will require additional JOINs.

    It will be good if we put these info along with the respective table (camera table in this example), than creating another lookuptable.
    Cheers....

    baburajv

  5. #5
    Join Date
    Nov 2011
    Posts
    6

    the question

    if i want to sort the cameras by ISO speed, so i want to show the client the ISO speeds values to choose.

    the question is if i want to sort also by resolution, or by any other attribute , should i have a table for each one and when i need to show that's values i will read from this table, what will bring to a situation that i have hundreds of tables
    ( ISO speed, resolution ,size...) or should i handle one huge table :

    AtributeName | Value
    --------------------
    Megapixels 1.34
    Megapixels 10.37
    Megapixels 12.4
    .
    .
    Resolution 50
    Resolution 200
    Resolution 300

    thanks you!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one problem with the huge table approach is that you won't be able to sort the values correctly, because they are all perforce stored in a varchar

    do some research: "one true lookup table"

    this type of design is generally frowned upon
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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