Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    32

    Best design help please..

    Hi All,

    I want to be able to store different types of information for products in different categories.

    So Category A maybe TV's and category B maybe DVD players, I want to be able to store different types of information on each product in that category. So for TV I may want to store screen size etc.. and for DVD players I may want to store if it supports upscaling etc.

    Obviousely each peice of info will be of a different data type but the reason I ask how best to store this is because I will have lots of categories of products and the products will be used for filtering too. So I need to create a solution which is not too costly on performance.

    Thanks in advance, any guidance even to any online tutorial would be appreciated.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Best design help ???...

    Probably a long way from best, but maybe a simple parent-child relationship? With common product information in the parent table (name, product number, classification...,) and product specific information in the child table, along with the presentation order... (ParentID, PresentationOrder, parameter, parameter description...)

    a workable solution,anyway.

    Ref Fundamentals of Database Design, by Paul Litwin for starters
    Last edited by loquin; 03-07-08 at 15:46.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by gaj
    I want to be able to store different types of information on each product in that category.

    <<SNIP>>

    So I need to create a solution which is not too costly on performance.
    There are two basic ways to look at this kind of problem.

    If you want to optimize performance for INSERT operations, you can stuff some XML into each row. This is utter anarchy from a data management perspective, but it sure is fast.

    If you want to optimize SELECT, UPDATE, and DELETE operations, you can use entity decomposition. This means that each new class of objects needs a new table added to your schema, but it means that you can then manage the resulting data.

    -PatP

  4. #4
    Join Date
    Jul 2004
    Posts
    32
    Thank you both for your suggestions, just want to probe a little further to identify whats best...

    With regards to the XML, will this cause performance problems if 100s of people are hitting the db at once searching through the same category although this is interesting and I never considered it ..

    To give you an example lets look at for the desktop computers we will have the following product specific info to filter on:

    http://computing.kelkoo.co.uk/ctl/do...&pid2=12461415

    and for TV's we have
    http://audiovisual.kelkoo.co.uk/ctl/...&pid2=18704336


    In the second instance, of creating parent-child relationships, we could end up with thousands of tables, is this really the way to do it? have hundreds of tables for category specific details? and you would not know how many fields/params are sufficient.

    What do you think Kelkoo does? or look at this site..
    for sat navs the have the filtering on the left...
    http://shopcompare.eu/ash/search.php?phrase=GPS
    and for PDA you also get these..
    http://shopcompare.eu/ash/search.php...almtops&cid=28

    Thanks again for your help on this...

  5. #5
    Join Date
    Jul 2004
    Posts
    32
    Looking around and speaking to people I have been told that another option is to have 1 related table to the categories which stores category related details and also another table related to that storing its type. Anybody know any more about this?

    Is the xml and related table solution better

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by gaj
    With regards to the XML, will this cause performance problems if 100s of people are hitting the db at once searching through the same category
    In SQLSVR 2005 you can index components of your XML data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2004
    Posts
    32
    It sounds like thats the best way to do it, fortunately I using sql server so I can use that functionality.

    I would be interesting to learn more about how to do this using another table which stores fields related to product and category. Is there anyway of validating the data and does anybody use this? sure people who use mysql to store the data must do this?

    Thanks.
    Gaj
    Last edited by gaj; 03-10-08 at 12:55.

Posting Permissions

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