40 ms·
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
by rodeoclown 7y ago
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.
- m33k44 7y agoWhat are the pros and cons of this approach?
- dragonwriter 7y agoThe 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.