Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2007
    Posts
    50

    Unanswered: Parent/Child relationship in the same table for multiple tables?

    I need to add to my app a cause list that can be up to three layers deep.

    eg..

    Cause - Hardware
    Type - Server
    Model - DL380

    Now what would be the recommended way to store this data?
    Three tables (There might be more layers later, but I'm pushing against that) or one table with Parent/Child keys?

    The app will populate drop downs based on the previous selection.

    Many Thanks,

    Paul.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It totally hinges on whether or not you need to scale to greater layers in the future.
    If your business rule is "three, and only three layers", simplify your life by creating three layers. If your business rule is "any number of layers", simplify your life by creating a recursive table (or a nested set model).
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jul 2007
    Posts
    50
    I cannot be 100% sure that we will stick with three layers, you know what management types are like..

    I guess the best solution will be a recursive table.. This table should be fairly static after the initial load.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    write your queries to use eleven layers

    you will use LEFT OUTER JOINs so if the extra layers aren't there, nothing bad happens, but if they are there, then the query still works

    see Categories and Subcategories

    simple, yes?

    certainly a lot simpler than learning the nested set model, which, despite knowing about it for many years and even with the aid of Joe Celko's book Trees and Hierarchies in SQL, i have still yet to try
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry, Rudy, but I have to disagree with you here.
    If he has an unspecified number of layers, then he needs to write code that will handle N-layers. Not 11 layers.
    In 2005, he should be using Common Table Expressions for this. In 2000, he should use an accumulator table.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Jul 2007
    Posts
    50
    TBH at the moment I would only need code to select values based on it's Parent (or lack of it for the root level).
    I think that the recursive table will be the best option.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hey blindman, have you got any examples of using an "accumulator table" - this is not a phrase I have come across before...
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have to disagree with your disagreement

    if N means "3, maybe 4, in extremely rare circumstances 5, but never 6" then redesigning for unlimited layers is not really necessary

    i mean, look at the data he's already shown -- cause > type > model -- how much deeper can it realistically go? down to the colour of the ink on the label of the motherboard ?

    but yeah, let's see your accumulator, baby

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2007
    Posts
    50
    The three fields I gave were an example, but I cannot see it going much deeper..

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You've seen it. Hell, I think you even linked to it once.
    sqlblindman - Returning Child Records
    But I'd still use CTEs for 2005 now.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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