3 ms·

Your storage space concern is unfounded, particularly without denormalizing your relationships (to gain performance). It seems you have ‘animals’, some of whic

Your storage space concern is unfounded, particularly without denormalizing your relationships (to gain performance). It seems you have ‘animals’, some of which can be ‘pets’, but not all. You also have ‘dogs’, ‘cats’, ‘rabbits’, etc., which may or may not be pets, but are all animals. So, my questions to you, what attributes do all animals share? What attributes do all pets share? What attributes are unique to dogs? What attributes are unique to cats? There are two main ways I could model this in an RDBMS, but I need to know more specifics.

m33k44 7y ago
> What attributes do all pets share? Pets have owner, feeding time, outdoor time etc. > What attributes are unique to dogs? Type, Height, Medical record, Sex, Barking pitch etc. > What attributes are unique to cats? Type, Colour, Weight, Medical record, Sex etc.
- dillonmckay 7y ago
  So, since you listed ‘sex’ as a unique attribute for both cats and dogs, I think you need to alter your perspective. We want to model the relationships between entities, not necessarily represent OOP ‘objects’ in a DB. So, you should have an ‘animal’ table that would have fields for any attribute that is shared by all animals, such as ‘sex’, ‘medical record’, and ‘weight’ (I am assuming all animals will have a gender). Medical record would be a lookup key, probably not an integer, so you cannot enumerate or get an off by one error, use a UUID or hashid. I am assuming non-pets can have medical records. Your dog and cat tables would reference the animal table via a foreign key for those details. For cat type and dog type, I would make those foreign key references to lookup tables for each of the respective types. Animal table would also have a foreign key reference to the pet table, but this can be NULL, if the animal is not a pet. Thoughts?
  - m33k44 7y ago
    So something similar to what @rodeoclown described below?
    - dillonmckay 7y ago
      Exactly.

m33k44 7y ago

> What attributes do all pets share? Pets have owner, feeding time, outdoor time etc. > What attributes are unique to dogs? Type, Height, Medical record, Sex, Barking pitch etc. > What attributes are unique to cats? Type, Colour, Weight, Medical record, Sex etc.

dillonmckay 7y ago
So, since you listed ‘sex’ as a unique attribute for both cats and dogs, I think you need to alter your perspective. We want to model the relationships between entities, not necessarily represent OOP ‘objects’ in a DB. So, you should have an ‘animal’ table that would have fields for any attribute that is shared by all animals, such as ‘sex’, ‘medical record’, and ‘weight’ (I am assuming all animals will have a gender). Medical record would be a lookup key, probably not an integer, so you cannot enumerate or get an off by one error, use a UUID or hashid. I am assuming non-pets can have medical records. Your dog and cat tables would reference the animal table via a foreign key for those details. For cat type and dog type, I would make those foreign key references to lookup tables for each of the respective types. Animal table would also have a foreign key reference to the pet table, but this can be NULL, if the animal is not a pet. Thoughts?
- m33k44 7y ago
  So something similar to what @rodeoclown described below?
  - dillonmckay 7y ago
    Exactly.