Return to Answer

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 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.

Below is the SQL to construct the basic model. Please note:

This first set of tables are the lookup / types tables:

Please note:

Initial Notes

This first set of tables are the lookup / types tables:

Please note:

1. Regarding the possibility (and then difficulty) of handling disparate properties between types of Breed, it is possible to employ the same subclass / inheritance concept but with Breed as the main entity. In this setup the Breed table would have the properties common to all types of Breed (just like the Animal table) and RaceID would represent the type of Breed (same as it does in the Animal table). Then you would have subclass tables such as BreedCat, BreedDog, and so on. For smaller projects this might be considered "over-engineering", but it is being mentioned as an option for situations that would benefit from it.

2. For both approaches, it sometimes helps to create Views as short-cuts to the full entities. For example, consider:

    CREATE VIEW Cats AS
       SELECT  an.AnimalID,
               an.RaceID,
               an.Name,
               -- other "Animal" properties that are shared across "Race" types
               cat.CatBreedID,
               cat.HairColor
               -- other "Cat"-specific properties as needed
       FROM    Animal an
       INNER JOIN  AnimalCat cat
               ON  cat.AnimalID = an.AnimalID
       -- maybe add in JOIN(s) and field(s) for "Race" and/or "Breed"
   

1. Regarding the possibility (and then difficulty) of handling disparate properties between types of Breed, it is possible to employ the same subclass / inheritance concept but with Breed as the main entity. In this setup the Breed table would have the properties common to all types of Breed (just like the Animal table) and RaceID would represent the type of Breed (same as it does in the Animal table). Then you would have subclass tables such as BreedCat, BreedDog, and so on. For smaller projects this might be considered "over-engineering", but it is being mentioned as an option for situations that would benefit from it.

2. For both approaches, it sometimes helps to create Views as short-cuts to the full entities. For example, consider:

    CREATE VIEW Cats AS
       SELECT  an.AnimalID,
               an.RaceID,
               an.Name,
               -- other "Animal" properties that are shared across "Race" types
               cat.CatBreedID,
               cat.HairColor
               -- other "Cat"-specific properties as needed
       FROM    Animal an
       INNER JOIN  AnimalCat cat
               ON  cat.AnimalID = an.AnimalID
       -- maybe add in JOIN(s) and field(s) for "Race" and/or "Breed"
   

3. While not part of the logical entities, it is fairly common to have audit fields in the tables to at least get a sense of when the records are being inserted and updated. So in practical terms:

4. A CreatedDate field would be added to the Animal table. This field is not needed in any of the subclass tables (e.g. AnimalCat) as the rows being inserted for both tables should be done at the same time within a transaction.

5. A LastModifiedDate field would be added to the Animal table and all subclass tables. This field gets updated only if that particular table is updated: if an update occurs in AnimalCat but not in Animal for a particular AnimalID, then only the LastModifiedDate field in AnimalCat would be set.