locked
Advise on Table Parent Child relation RRS feed

  • Question

  • Dear all,

    we are creating a new database from scratch in order to provide a machine configuration toold.

    The genal idea is that we have different machine which are based on parameters and components

    The business rul is as below :

    - A machine contains a set of parameters and components

    - A component is an entity which describe a set on fonctionnal equipment for the machine

    - A component can be based on set of sub components

    - A machine contains a set of parameters

    To take a more concrete exemple imagine the followinf scenario :
    If you take a PC that you want to configure, PC component can be CPU card, memeory, hard disk, wifi module,...
    Each component can be use individually on same PC type ( for exemple you can need only hard disk or memory.
    Then if you provide a complete CPU cqrd as single component, CPU cqard will be based on sub component like Hard drive, memeory, grpahic cards,wiffi module. At this time the comopent in the CPU board and sub component are CPU board child component.

    What I have trouble to find out is how this Component qnd SubComponent table can be configured in order that each individuql component can be used alone or use as a Child component of a Parent Component;

    I guess it is quite similar configuration if you thing about Product which can be used alone in a quote or a ProductBundlle whcih is a kind of special product which has child

    Thansk for help and sample

    regards

    Friday, May 25, 2018 7:53 PM

Answers

  • Hi serge,

    Did you resolve your issue? If so, please kindly mark corresponding reply. It'll benefit others with relevant issue.

    If not, please feel free to ask.

    Thanks,
    Xi Jin.


    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.

    • Marked as answer by wakefun Monday, May 28, 2018 6:39 AM
    Monday, May 28, 2018 2:54 AM

All replies

  • You can simply store them inside same table using a self referencing relationship

    The table will be called say Components for example and it will have ComponentID and ParentComponentID within

    ParentComponentID will be optional and it refers to ComponentID within same table (self reference)

    The parent component (in your example CPUcard) will be an entry in this table with a componetid and parentcomponentid will be NULL (indicating that its the parent)

    For every component within it, they will all have unique entries within the table with different ComponentIDs but ParentComponentID for all them would be the same and will be same as ComponentID of the entry of the Parent you created above

    this way you can keep linking to any level to indicate parent, its child, childs child etc depending on how much level the relationship can extend

    While fetching results you can use a recursive logic as explained below using common table expressions to get a parent and all its descendants or vice versa (from child to all its ancestors above)

    https://www.mssqltips.com/sqlservertip/1520/recursive-queries-using-common-table-expressions-cte-in-sql-server/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, May 25, 2018 9:56 PM
  • I understand your points but would it be more simple to get  a separate tables which handle Parent-Child relashion ship which will not be recursive but will simplify return data querry ?

    thanks for sample and advise

    Saturday, May 26, 2018 8:15 AM
  • I understand your points but would it be more simple to get  a separate tables which handle Parent-Child relashion ship which will not be recursive but will simplify return data querry ?

    thanks for sample and advise

    That is more suited for the cases where the depth of relationship is fixed i.e always only say 1 or 2 level of child etc

    If you cant determine level of depth before hand, recursive approach will give you the maximum flexibility.

    So based on your use case, you can use any one of the methodology

    I suggested it from a more generic perspective


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, May 26, 2018 8:22 AM
  • ok noted but I try to undestand:

    you said :

    "That is more suited for the cases where the depth of relationship is fixed i.e always only say 1 or 2 level of child etc".

    How can you justify that it is not suitable solution if more than 1 pr 2 level ? based on measure experience or others ? can you explan more ?

    regrds

    Sunday, May 27, 2018 4:18 PM
  • ok noted but I try to undestand:

    you said :

    "That is more suited for the cases where the depth of relationship is fixed i.e always only say 1 or 2 level of child etc".

    How can you justify that it is not suitable solution if more than 1 pr 2 level ? based on measure experience or others ? can you explan more ?

    regrds

    If you're not sure on the depth then how do you design tables? since you use separate tables for every depth you've to create a new table right? If level of depth is unknown how will you determine number of table to be created

    Also its not scalable as each time level increases or decreases you've keep on changing your model (adding/removing tables)

    In the other methodology all you need to do is to add or remove rows in a existing table which is straightforward and easily scalable


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Xi Jin Monday, May 28, 2018 2:51 AM
    Sunday, May 27, 2018 4:21 PM
  • That is clear now I get it.

    Thanks for that

    regards

    serge

    Sunday, May 27, 2018 8:02 PM
  • Hi serge,

    Did you resolve your issue? If so, please kindly mark corresponding reply. It'll benefit others with relevant issue.

    If not, please feel free to ask.

    Thanks,
    Xi Jin.


    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.

    • Marked as answer by wakefun Monday, May 28, 2018 6:39 AM
    Monday, May 28, 2018 2:54 AM
  • That is clear now I get it.

    Thanks for that

    regards

    serge

    Pleas mark the relevant posts as answer if it gave you the solution

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, May 28, 2018 7:02 AM