Hacker News Reader

Comment by eb0la | Hacker News Reader

I agree if you can use a recursive table, do it. If you're using an ORM do it as the ORM allows you and think about performance later as soon as it grows big.

If tables are small (or you have good indexes), a non-optimal structure will still work for you.

View on HN

  > I agree if you can use a recursive table

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

  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