Ask HN: How do I represent a hierarchy in RDBMS?

Re:Ask HN: How do I represent a hierarchy in RDBMS? by rodeoclown on Tuesday May 28, 2019 @01:23PM (#20032221)

Start with the animal table:

Animal(animal_id PK, ... animal attributes)

No need for join tables or surrogate keys on these tables. The animal_id can serve as both the primary key and foreign key.

Dog(animal_id PK FK, ... dog only attributes) Cat(animal_id PK FK, ... cat only attributes)

No need for surrogate key on pet, and don't forget the owner key:

Pet(owner_id PK FK, animal_id PK FK)

This allows multiple owners of one animal. If you don't want that you need a unique constraint on animal_id.

[ Parent | on HN ]

Re:Ask HN: How do I represent a hierarchy in RDBMS? by m33k44 on Wednesday May 29, 2019 @02:33AM (#20037343)

What are the pros and cons of this approach?

[ Parent | on HN ]

Re:Ask HN: How do I represent a hierarchy in RDBMS? by dragonwriter on Wednesday May 29, 2019 @05:38PM (#20045814)

The pros are you aren't creating unnecessary sequences and tables, which has both storage space and join complexity savings.

The cons are...none, really; the extra surrogate keys aren't buying you anything.

[ Parent | on HN ]