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