How to implement a super class, sub class relationship in the database?

Question

If I have a class called animal, dog and fish is the subclass. The animal have attribute called "color". Dog have the attribute called "tail length", and the fish don't have this attribute. Fish have the attribute called "weight", the dog don't have this attribute.

So, I want to design a database to store this information. What should I do? Here is some ideas:

Idea 1: Making an animal table, and the table have type, to find what kind of animal, if it is a dog, just get the result from dog table.

Animal: color:String type:int

Type: Dog:0 Fish:1

Dog: TailLength:int

Fish: Weight:int

Idea 2: Store only Dog table and Fish table in the database, remove the animal table.

Dog: Color: String TailLength: int

Fish: Color: String Weight: int

Answer 1

You could try it like this:

Animal
    PK animal_id
    FK animal_type
    STRING animal_name (eg. 'Lassie')

AnimalTypes
    PK animal_type
    STRING animal_type_name (eg. 'Dog')

AnimalAttributes
    PK attribute_id
    STRING attribute_name (eg. 'tail length')

AnimalToAttributes
    PK id
    FK animal_id
    FK attribute_id
    INTEGER value (eg. 20)

This way you can have one or many attributes per animal (it's up to you to choose).

Answer 2

Use a one to zero or one relationship As you note, In database schema design language the tables are called class - sub-class or superclass

   Create Table Animal
    (animalId Integer Primary Key Not null,
     Other columns generic to all animals)

   Create Table Birds
    (BirdId Integer Primary Key Not Null 
     references Animal(AnimalId),
     -- other columns)

Answer 3

The two approaches you mentioned:

• One table representing objects in the entire inheritance hierarchy, with all the columns you'd need for the entire hierarchy plus a "type" column to tell you which subclass a particular object is.
• One table for each concrete class in your inheritance hierarchy, with duplicated schema.

can be supplemented by two others:

• One table for each class in your inheritance hierarchy – you now have an Animal table, and subclasses have foreign keys that point to the common set of data in Animal.
• Generic schema – have a table to store objects, and an attribute table to support any set of attributes attached to that object.

Each approach has pros and cons. There's a good rundown of them here:

• http://www.agiledata.org/essays/mappingObjects.html#ComparingTheStrategies

Also take a look at these SO topics:

• http://stackoverflow.com/questions/554522/something-like-inheritance-in-database-design
• http://stackoverflow.com/questions/1337751/help-me-to-connect-inheritance-and-relational-concepts
• http://stackoverflow.com/questions/600684/object-oriented-like-structures-in-relational-databases
• http://stackoverflow.com/questions/1567935/how-to-do-inheritance-modeling-in-relational-databases
• http://stackoverflow.com/questions/190296/how-do-you-effectively-model-inheritance-in-a-database

Finally, it should be noted that there are object-oriented databases out there that represent objects more naturally in the database, and could easily solve this problem, though they are not nearly as frequently used in the industry. Here are some links that describe such DBs as compared to relational DBs, though they won't give you an entirely objective (heh) view on the matter:

• http://stackoverflow.com/questions/800/object-oriented-vs-relational-databases
• http://stackoverflow.com/questions/3000560/object-oriented-database-why-most-of-the-companies-do-not-use-them