1

first at all, I have NO IDEA how to title my question. If anyone has a better title I'll be greatfull.

My real question now, is about what is the best approach to make records referencing other records in the same table. I'm thinking about something like

  • Person
  • A person must have a father (logical thinking, let's forget about orphan people, please)
  • A person can be parent to many childs

The table could be somehting like PersonId, Name, PersonParentId

Is it right? There is a better way to do it? I heard about Hierarchical Data but I'm not sure.

Thanks

Leandro De Mello Fagundes
  • 500
  • 1
  • 8
  • 34
  • 1
    That's a good start! Don't forget the foreign key! – jarlh Feb 13 '17 at 15:16
  • 1
    What you're suggesting is perfectly fine. – Siyual Feb 13 '17 at 15:17
  • It's all? Easy and simple? I'm scared how simple it can be :D – Leandro De Mello Fagundes Feb 13 '17 at 15:18
  • 1
    This sounds fine. This is known as an adjacency list. It is quite common. Another option is to use nested sets. And yet another option is to use the hierarchyid datatype in sql server. Nested sets are pretty sweet because you can avoid the need for recursive ctes to retrieve the tree. – Sean Lange Feb 13 '17 at 15:24
  • Reading right now about both of other options! Thanks for the infos, I like to know a lot about the options :) – Leandro De Mello Fagundes Feb 13 '17 at 15:26
  • 2
    The form that you are describing is called an **adjacency list**. Learn more about it and other forms starting here: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database or here: [Louis Davidson - Presentations - How to Optimize a Hierarchy In SQL Server - Presentations & Demo Code](http://www.drsql.org/Pages/Presentations.aspx) – SqlZim Feb 13 '17 at 15:50

1 Answers1

3

This is almost always the best way to do it. You will see discussions of better ways but in general, this is almost always the right way to do it.

A few pointers.

  1. Don't forget the foreign key
  2. Make sure the referential logic is solid
  3. Look into WITH RECURSIVE for tree-based queries
  4. In your Recursive CTE's code defensively and check for cycles.
Chris Travers
  • 25,424
  • 6
  • 65
  • 182
  • 1
    I would argue pretty strongly in favor of nested sets. It eliminates the need for recursion and moving pieces of the tree are really simple. – Sean Lange Feb 13 '17 at 15:27
  • 2
    @SeanLange would you argue for nested sets strongly on large sets with a high level of inserts? – SqlZim Feb 13 '17 at 15:48
  • 2
    @SqlZim as with anything and everything in sql server the only valid answer to your question is "it depends". Most trees like this are not subject to high volumes of inserts and few selects. They are usually the opposite, low amounts of inserts and lots of selects. – Sean Lange Feb 13 '17 at 15:52
  • @SeanLange thanks for give your opinion. I read a little about it. Here comes a small benchmark that shows the adjacent gives us better results. Do you know anything else, or can explain with real life examples why do you prefer nested? https://explainextended.com/2009/09/25/adjacency-list-vs-nested-sets-sql-server/ – Leandro De Mello Fagundes Feb 13 '17 at 15:59
  • 1
    Well for example if you want to find a given parent and all children it can be done with a single simple query, no need for recursion. I have used both adjacency lists and nested sets many times. They both have advantages and disadvantages. Nested sets are a little more complicated to wrap your head around initially and there is nothing wrong adjacency lists. The best approach is the one best suited your situation. – Sean Lange Feb 13 '17 at 16:14