If I've got a class known as animal, dog and seafood may be the subclass. Your pet have attribute known as "color". Dog possess the attribute known as "tail length", and also the seafood do not have this attribute. Seafood possess the attribute known as "weight", your dog do not have this attribute.

So, I wish to design a database to keep these details. What must i do? Here's ideas:

Idea 1: Making a pet table, and also the table have type, to locate what type of animal, if it's your dog, just obtain the derive from dog table.

Animal: color:String type:int

Type: Dog: Seafood:1

Dog: TailLength:int

Seafood: Weight:int

Idea 2: Store only Dog table and Seafood table within the database, take away the animal table.

Dog: Color: String TailLength: int

Seafood: Color: String Weight: int

The 2 approaches you pointed out:

  • One table representing objects within the entire inheritance hierarchy, with the posts you'd demand for entire hierarchy along with a "type" column to inform you which ones subclass a specific object is.
  • One table for every concrete class inside your inheritance hierarchy, with copied schema.

could be compounded by two others:

  • One table for every class inside your inheritance hierarchy – you have an Animal table, and subclasses have foreign secrets that could indicat the most popular group of data in Animal.
  • Generic schema – possess a table to keep objects, as well as an attribute table to aid any group of characteristics mounted on that object.

Each approach has benefits and drawbacks. There is a good rundown of these here:

Also have a look at these SO subjects:

Finally, it ought to be noted that you will find object-oriented databases available that represent objects more naturally within the database, and may easily solve this issue, though they aren't as commonly used in the market. Here are a few links that describe such DBs as in comparison to relational DBs, though they will not provide you with a completely objective (heh) take on the problem:

You could attempt it such as this:

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

    PK animal_type
    STRING animal_type_name (eg. 'Dog')

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

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

By doing this you could have one or many characteristics per animal (the choice is yours to select).