Ask HN: DB wizards of HN, how do I represent a hierarchy in RDBMS?
6 by m33k44 | 9 comments on Hacker News.
Say, I have following hierarchy: Animal ==+==> Dog | +==> Cat and another "class", Pet, that references Animal. Pet --1-----+--> Animal The way I can represent this in RDBMS is by creating tables for each class, Pet, Animal, Cat and Dog. And then additional tables for the relationships, i.e. PetAnimal(pet_id, animal_id) [one-to-many] AnimalDog(animal_id, dog_id) [one-to-one] AnimalCat(animal_id, cat_id) [one-to-one] This way I am able to represent the hierarchy of Animal, Dog and Cat. So, tomorrow if I want to add Rabbit, then I will just add Rabbit and AnimalRabbit tables. My only concern is that the Animal table will grow rapidly as more pets and animal types are added and will be a performance issue. What is a better way to represent hierarchical structures in RDBMS to avoid both storage space explosion and performance issues?
6 by m33k44 | 9 comments on Hacker News.
Say, I have following hierarchy: Animal ==+==> Dog | +==> Cat and another "class", Pet, that references Animal. Pet --1-----+--> Animal The way I can represent this in RDBMS is by creating tables for each class, Pet, Animal, Cat and Dog. And then additional tables for the relationships, i.e. PetAnimal(pet_id, animal_id) [one-to-many] AnimalDog(animal_id, dog_id) [one-to-one] AnimalCat(animal_id, cat_id) [one-to-one] This way I am able to represent the hierarchy of Animal, Dog and Cat. So, tomorrow if I want to add Rabbit, then I will just add Rabbit and AnimalRabbit tables. My only concern is that the Animal table will grow rapidly as more pets and animal types are added and will be a performance issue. What is a better way to represent hierarchical structures in RDBMS to avoid both storage space explosion and performance issues?
Comments
Post a Comment