locked
Advise on Table design RRS feed

  • Question

  • Dear all,

    I have a pool of data which are based organized has CATEGORY and SUB-CATEGORY. When I am configuring the database I should be able to define all categories at once, then being able to define which one is the Parent category of the others.

    For exemple if I have a set of Dishes for a restaurant, then I can classify those dishes in different menu category which are then the Parents category for dishes.

    Please note that a SUB-CATEGORY can have other sub categories attached.

    To have a better idea of what I am trying to do is similar things when you create folders and sub folders on a file system. From the configuration side of the database I should be able to define my category and subcategories and then organize them by a link ID or other stuff, in the same way I will organize folder.

    What will be the best Database table design for that scenario ?

    I am not a DBA, but have some basis so try to speak the same language as me :-)

    Thanks for help

    regards

    Monday, February 17, 2014 11:26 AM

Answers

  • Check out the Product, ProductSubcategory and ProductCategory hierarchical tables in AdventureWorks2012.

    Product table has an FK to ProductSubcategory and ProductSubcategory has an FK to ProductCategory.

    Example of query with subcategory and category information:

    http://www.sqlusa.com/bestpractices2005/orderbyinview/


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Monday, February 17, 2014 12:34 PM
  • Please see:

    http://www.microsoft.com/en-us/download/details.aspx?id=10331

    Yes, there is a reason.  It makes everything easier than creating multiple tables with the same structure for multiple addresses.

    Monday, February 17, 2014 2:33 PM
    Answerer
  • As per you explanation you wanted parent child relationship between categories. This can be done by adding CategoryID and ParentCategopryID fields within table and including a foreignkey relationship from ParentCategoryID field to CategoryID field of same table. This is known as self referencing relationship.

    So if you want to say represent menu items as Dishes -> Starters-> Item1 you can represent it in your table as follows

    CategoryID    CategoryName    ParentCategoryID
    ------------  --------------  -----------------
    1001          Dishes          NULL
    1002          Starters        1001
    1003          Item1           1002

    this will ensure the parent child relationship is properly represented in table

    Now if you want to represent subcategories within a cetgory you can create subcategory table with fields (SubCategoryID,SubCategoryName,CategoryID)

    So you can represent subcategories within categpry as below

    SubCategoryID    SubCategoryName    CategoryID
    ------------  --------------        -----------
    10001          SubCat1               1003
    10002           SubCat2              1003 
    10003           SubCat3              1003

    and if you want parent child relationship here also add parentsubcategoryid and link it to subcategoryid just like you did it in category table


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, February 18, 2014 11:23 AM
  • I think you have it with your design. There are a few ways to represent a hierarchy in SQL, and having two tables like this:

    CategoryID    CategoryName    ParentCategoryID
    ------------  --------------  -----------------
    1001          Dishes          NULL
    1002          Desserts        1001
    1003          Icecream           1002

    SubCategoryID    SubCategoryName    CategoryID
    ------------             --------------                -----------
    10001                   Vanilla                      1003
    10002                   Strawberry               1003
    10003                   Butterscotch             1003

    Make sense ONLY if you can guarantee a two level hierarchy (or a fixed number of levels, where you might have yet another table SubSubCategory). Having a fixed number of levels can make processing a ton easier because your queries can be written in a straightforward manner because the levels of nodes in the tree  remain the same.

    Your requirements here:

    "CATEGORY can have other sub categories attached."

    Leads to the more flexible solution like you have set up:

    CategoryID    CategoryName    ParentCategoryID
    ------------  --------------  -----------------
    1001          Dishes          NULL
    1002          Starters        1001
    1003          Item1           1002
    1004          SubCat1         1003
    1005          SubCat2         1003
    1006          SubCat3         1003

    Because adding a subcategory on SubCat3 is as simple as:

    1007          SubSubCat1         1006

    Processing the hierarchy becomes an iterative process (using a recursive CTE) if you have needs to do a lot of aggregation on groups. There are techniques available to make things go faster than using this pattern in the implementation, but unless you are going to need 1000s of nodes, then this pattern works great (known as the adjacency list, where you are basically storing the adjacent node in your tree).


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Monday, March 3, 2014 7:20 PM

All replies

  • I think for your scenario what you could do is make  self referenced relationships in table. ie have fields like CategoryID and ParentCategoryID in Categories to indicate parent child relationships.

    In SUbCategory table have a field called CategoryID which is linked to CategoryID in Categories table by means of FK relationsip. Then if you want to have parent child relationship here also add two fields SubCatID, ParentSubCatID to related subcategories together as Parent and child and relate to same table.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, February 17, 2014 12:26 PM
  • Check out the Product, ProductSubcategory and ProductCategory hierarchical tables in AdventureWorks2012.

    Product table has an FK to ProductSubcategory and ProductSubcategory has an FK to ProductCategory.

    Example of query with subcategory and category information:

    http://www.sqlusa.com/bestpractices2005/orderbyinview/


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Monday, February 17, 2014 12:34 PM
  • Hello,

    Is there a database drawing describing the Adventureworks database to better understand it ?

    Just get through some table and get a basic question. is there any reason that the identification of a person, address and stuff is splitted in so many different table ?

    regards

    Monday, February 17, 2014 2:24 PM
  • Please see:

    http://www.microsoft.com/en-us/download/details.aspx?id=10331

    Yes, there is a reason.  It makes everything easier than creating multiple tables with the same structure for multiple addresses.

    Monday, February 17, 2014 2:33 PM
    Answerer
  • Thnaks for the link.

    Any idea what is the use of the rowgui id field in most table ?

    Monday, February 17, 2014 2:47 PM
  • The rowguid is used for unique multiple server transfer, like merge replication.  It is almost guaranteed unique between servers.

    Monday, February 17, 2014 2:59 PM
    Answerer
  • All right then it is only dedicated to replication between servers or stuff like this, correct? so I I do not have such feature I can simply ignore that..

    Sorry for basic question but I am also learning on the structure design and need to understand the approach. For instance in some other database that we do, when we have different Adress Type that an Adress table refers, most of the time we add the FK in Adress Table to Adress Type table.

    IN the case of AW database, I see that the FK for address Type is inside Customer Adress table instead, any reason for that ? Does my previous design is also correct or better follow the one from AW ?

    regards

    Monday, February 17, 2014 3:12 PM
  • You should follow the example in the AW sample.  Not everything is perfect, but there was many hours of proper database design to produce the AW sample.

    • Marked as answer by wakefun Monday, February 17, 2014 3:41 PM
    • Unmarked as answer by wakefun Tuesday, February 18, 2014 10:48 AM
    Monday, February 17, 2014 3:19 PM
    Answerer
  • Hello Visak, cannot see really what you mean could you explain with a small drawing to help me catch it ? sorry for basic question but I am not so a DBA and some time playing with different structure ideas is condusing me

    thanks

    Tuesday, February 18, 2014 10:42 AM
  • Hello Kalman,

    I see different implementation of Product Categories and sub categories depending if I check on the OLP version of the light version of AW.

    I am sorry to come back to basic things but based on the structure I cannot clearly see how a sub categorie can have other sub categories.

    In fact in my case, a category can have more than 1 sub category and a sub category can have also one or more sub categories level.

    For instance in Light version of AW there is a single table which identify a categorie and sub category relationship but then in the OLP version the structure is more complex.

    Which one should I go for my scenario ?

    thanks for help

    regards


    • Edited by wakefun Tuesday, February 18, 2014 11:13 AM
    Tuesday, February 18, 2014 10:54 AM
  • As per you explanation you wanted parent child relationship between categories. This can be done by adding CategoryID and ParentCategopryID fields within table and including a foreignkey relationship from ParentCategoryID field to CategoryID field of same table. This is known as self referencing relationship.

    So if you want to say represent menu items as Dishes -> Starters-> Item1 you can represent it in your table as follows

    CategoryID    CategoryName    ParentCategoryID
    ------------  --------------  -----------------
    1001          Dishes          NULL
    1002          Starters        1001
    1003          Item1           1002

    this will ensure the parent child relationship is properly represented in table

    Now if you want to represent subcategories within a cetgory you can create subcategory table with fields (SubCategoryID,SubCategoryName,CategoryID)

    So you can represent subcategories within categpry as below

    SubCategoryID    SubCategoryName    CategoryID
    ------------  --------------        -----------
    10001          SubCat1               1003
    10002           SubCat2              1003 
    10003           SubCat3              1003

    and if you want parent child relationship here also add parentsubcategoryid and link it to subcategoryid just like you did it in category table


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, February 18, 2014 11:23 AM
  • hello Visak, thanks for your explaination.

    First table structure explaination is clear now but I do not see when you wuld use the the second table for SUb-Categories ?

    I could also do that :

    CategoryID    CategoryName    ParentCategoryID
    ------------  --------------  -----------------
    1001          Dishes          NULL
    1002          Starters        1001
    1003          Item1           1002
    1004          SubCat1         1003
    1005          SubCat2         1003
    1006          SubCat3         1003 

    what would be the use of the Sub Category tables ? in whish scenarios

    regards

    Tuesday, February 18, 2014 12:10 PM
  • in cases where you want to store detail level data under each category  (I added the table as per your first post explanation only)

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, February 18, 2014 12:33 PM
  • Sub Categories tables extra tables confuse me, sorry.
    I do not see what it will do extra compare to self reference table.

    Could you please take an exemple ?

    regards

    • Edited by wakefun Tuesday, February 18, 2014 1:12 PM
    Tuesday, February 18, 2014 1:09 PM
  • say in one of your dishes you've subitems like

    CategoryID    CategoryName    ParentCategoryID
    ------------  --------------  -----------------
    1001          Dishes          NULL
    1002          Desserts        1001
    1003          Icecream           1002

    Then inside icecream you've different subcategories like Vanilla,Strawberry,Butterscotch etc . then you can represent them inside subcategory table as

    SubCategoryID    SubCategoryName    CategoryID
    ------------             --------------                -----------
    10001                   Vanilla                      1003
    10002                   Strawberry               1003
    10003                   Butterscotch             1003


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, February 18, 2014 1:17 PM
  • ok I got the idea but I could do the same like this :

    CategoryID    CategoryName    ParentCategoryID
    ------------  --------------  -----------------
    1001          Dishes          NULL
    1002          Desserts        1001
    1003          Icecream        1002
    1004          Valilla         1003
    1005          Strawburry      1003
    1006          BUtterscotch    1003

    It will work the same no ? or did I miss something

    Or maybe you try to explain that if for a category in the self referencing table we reach the last node where category details will be, then better to add a Sub category table as a last node ? is that what you try to explain ?


    • Edited by wakefun Tuesday, February 18, 2014 2:09 PM
    Tuesday, February 18, 2014 2:07 PM
  • I think you have it with your design. There are a few ways to represent a hierarchy in SQL, and having two tables like this:

    CategoryID    CategoryName    ParentCategoryID
    ------------  --------------  -----------------
    1001          Dishes          NULL
    1002          Desserts        1001
    1003          Icecream           1002

    SubCategoryID    SubCategoryName    CategoryID
    ------------             --------------                -----------
    10001                   Vanilla                      1003
    10002                   Strawberry               1003
    10003                   Butterscotch             1003

    Make sense ONLY if you can guarantee a two level hierarchy (or a fixed number of levels, where you might have yet another table SubSubCategory). Having a fixed number of levels can make processing a ton easier because your queries can be written in a straightforward manner because the levels of nodes in the tree  remain the same.

    Your requirements here:

    "CATEGORY can have other sub categories attached."

    Leads to the more flexible solution like you have set up:

    CategoryID    CategoryName    ParentCategoryID
    ------------  --------------  -----------------
    1001          Dishes          NULL
    1002          Starters        1001
    1003          Item1           1002
    1004          SubCat1         1003
    1005          SubCat2         1003
    1006          SubCat3         1003

    Because adding a subcategory on SubCat3 is as simple as:

    1007          SubSubCat1         1006

    Processing the hierarchy becomes an iterative process (using a recursive CTE) if you have needs to do a lot of aggregation on groups. There are techniques available to make things go faster than using this pattern in the implementation, but unless you are going to need 1000s of nodes, then this pattern works great (known as the adjacency list, where you are basically storing the adjacent node in your tree).


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Monday, March 3, 2014 7:20 PM
  • ello Visak, thanks for your explaination.

    First table structure explaination is clear now but I do not see when you wuld use the the second table for SUb-Categories ?

    I could also do that :

    CategoryID    CategoryName    ParentCategoryID
    ------------  --------------  -----------------
    1001          Dishes          NULL
    1002          Starters        1001
    1003          Item1           1002
    1004          SubCat1         1003
    1005          SubCat2         1003
    1006          SubCat3         1003 

    what would be the use of the Sub Category tables ? in whish scenarios

    regard

    this method is the best method.

    I already have a knowledge base which I developed it in the C#. it has tree view and user is able to add as many sub-group as required in the database.

    you need only one table.



    • Edited by Alex_0s Monday, March 3, 2014 8:26 PM
    Monday, March 3, 2014 8:25 PM
  • wakefun,

    As you said, you CAN have the single table structure. Yes , it does cater to your requirement in a clear way.

    But you might have to resort to Visakh's design in case of the following:

    • You have additional columns of detail to be stored for your sub categories. For example, the icecream sub category stores flavours. You might wanna store whether they are seasonal or not or maybe the source of these flavours.

          (These are additional columns of detail that are applicable only to sub categories and not categories as a whole. If you have these additional columns as apart fo the Category table, unnecessarily there'll be  alot of NULLs for all the categories.)

    • You want to restrict the level of hierarchy. As indicated by visakh, this will be of great help with respect to querying your data at a later stage. But yet, this again depends on the business purpose. If your business may contain a dynamic/unknown number of sublevels, then this point will not be applicable for you.

    That being said, go for your chocie of design as per your business requirements. the one table structure shud work fine PROVIDED there arent any cases as listed in the above points and as well as mentioned by others.


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Tuesday, March 4, 2014 10:34 AM