The proper structure for this scenario is a SubClass / Inheritance model, and is nearly identical to the concept I proposed in this answer: Heterogeneous ordered list of value.
The model proposed in this question is actually quite close in that the Animal
entity contains the type (i.e. race
) and the properties that are common across all types. However, there are two minor changes that are needed:
- Remove the Cat_ID and Dog_ID fields from their respective entities:
The key concept here is that everything is an Animal
, regardless of race
: Cat
, Dog
, Elephant
, and so on. Given that starting point, any particular race
of Animal
doesn't truly need a separate identifier since:
- the
Animal_ID
is unique - the
Cat
,Dog
, and any additionalrace
entities added in the future do not, by themselves, fully represent any particularAnimal
; they only have meaning when used in combination with the information contained in the parent entity,Animal
.
Hence, the Animal_ID
property in the Cat
, Dog
, etc entities is both the PK and the FK back to the Animal
entity.
- Differentiate between types of
breed
:
Just because two properties share the same name does not necessarily mean that those properties are the same, even if the name being the same implies such a relationship. In this case, what you really have is actually CatBreed
and DogBreed
as seperate "types"
{will update shortly with the appropriate SQL}
Additional Notes:
- The concept of
breed
seems to be a focal point for confusion. It was suggested by jcolebrand (in a comment on the question) thatbreed
is a property shared across the differentrace
s, and the other two answers have it integrated as such in their models. This is a mistake, however, because the values forbreed
are not shared across the different values ofrace
. Yes, I am aware that the two other proposed models attempt to solve this issue by makingrace
a parent ofbreed
. While that technically solves the relationship issue, it doesn't help solve the overall modeling question of what to do about non-common properties, nor how to handle arace
that does not have abreed
. But, in the case that such a property were guaranteed to exist across allAnimal
s, I will include an option for that as well. - The models proposed by vijayp and DavidN (which seem to be identical) do not work because:
- They either
- do not allow for non-common properties to be stored (at least not for individual instances of any
Animal
), or - require that all properties for all
race
s be stored in theAnimal
entity which is a very flat (and nearly non-relational) way of representing this data. Yes, people do this all of the time, but it means having many NULL fields per row for the properties that are not meant for that particularrace
AND knowing which fields per row are associated with the particularrace
of that record.
- do not allow for non-common properties to be stored (at least not for individual instances of any
- They do not allow for adding a
race
ofAnimal
in the future that does not havebreed
as a property. And even if ALLAnimal
s have abreed
, that wouldn't change the structure due to what has been previously noted aboutbreed
: thatbreed
is dependent on therace
(i.e.breed
forCat
is not the same thing asbreed
forDog
).