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

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

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 tables with 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:

• Something like inheritance in database design
• Help me to connect inheritance and relational concepts
• Object-oriented-like structures in relational databases
• How to do Inheritance Modeling in Relational Databases?
• How do you effectively model inheritance in a database?

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

• What is the difference between graph-based databases and object-oriented databases?
• "In defense of the RDBMS", Gavin King, 2007
• "The relational database needs no defense" (in support of OODBMSes), Ted Neward, 2007
• Object Oriented Database - why most of the companies do not use them

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

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)