Hacker News Reader

Comment by m33k44 | Hacker News Reader

  > I agree if you can use a recursive table

Could you please elaborate on that? Which table will have recursive relation in this case?

View on HN

  Category ID  Category Name Category Parent
  -----------  ------------- ---------------
  1            Animal        NULL
  2            Dog           1
  3            Cat           1
  4            Rabbit        1
  5            Beagle        2
  6            Lion          3
  7            Tiger         3
  8            Bengal Tiger  7

A Bengal Tiger (8) is a Tiger (7) is a Cat (3) is an Animal (1).

just for your sanity in a relational database alway put a root id for hierarchies. Also, you might want to skip the null and use a self reference depending on how you need to build the hierarchy. Root only matters if you have multiple hierarchies in a table.

+1 for root-id, this is a huge failing in a lot of threaded discussion table designs.

Yeah, it really helps if you have to do some processing in the stored procedures and it helps with the query speed if properly indexed. I have seen folks put a level number in the table also, but that really depends on what the hierarchy represents.

Reply on news.ycombinator.com