none
Best practice RRS feed

  • Question

  • Hi all,

    I am trying to get best design for DB. I need Products table, and would like to have some attributes, yet they are different for each product type. I have few products types. I know that this can be done with  single table but I would like to know if there is a better approach. Thanks. 

    Monday, May 25, 2020 8:10 PM

All replies

  • I am trying to get best design for DB. I need Products table, and would like to have some attributes, yet they are different for each product type. I have few products types. I know that this can be done with  single table but I would like to know if there is a better approach. Thanks. 

    It is very difficult to say what is the best for you from this small amount of details.

    It is not uncommon to do this supertables and subtables. In this approach you have a table Product with the main attribute that applies to all products. Then you have one subtable per product type with the specific attributes. If some product types have the same attributes you can lump them into the same table. You could also have sub-sub-tables is some types have a couple of attibutes in common but also some specific attributes.

    Basically, this design mimicks how you would set up an object hierarchy.

    But this model may not work well, if the product types are less well-defined, or if there are very many types. Having fifty subtables is probably not appealing.

    Another factor here is what you will use the attributes for. Will to make computations from them? Will they control business rules? Or are they just part of a set of values to present?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, May 25, 2020 9:50 PM
  • Hi VojoCeklic,

    Please refer to the following similar posts:
    How to design a product table for many kinds of product where each product has many parameters

    Database Design - products attributes

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 26, 2020 6:39 AM
  • Erland,

    Thank for your reply. It is helpping me to start thinking about it in a different way. Curently there are just few categories, but it might grow. Some of attributes might be used to do computations. I am mainly puzzled with querrying data and inserts, when supertables and subtables are used. Since it ia a small project I could all put in single table and have raw distingtion with Product type, yet I am trying to find best approach for this as far as balance of simplicity and performace goes. 

    Tuesday, May 26, 2020 6:48 AM
  • Thanks a lot Chris. I did see that post, yet it does not give an opinion at least I was not able to understand what was the best. I will try some of those and see. Thanks.
    Tuesday, May 26, 2020 3:12 PM
  • There is not one answer to your question.  There are pros and cons to every approach.

    Your products should have a common set of values, quantity, unit, description, sku, category, sub-category, etc. 

    Then you have things specific to that item or category of items.  The simplest approach is to create a key value pair table for those values.  However, that can be difficult to filter items (if you need too). 

    If you have fixed properties per type, I would do what Erland suggested and create a table per type.



    Tuesday, May 26, 2020 5:37 PM
    Answerer