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
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:
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)
By doing this you could have one or many characteristics per animal (the choice is yours to select).