How to redesign this SQL data structure?

Question

I have a data structure used for storing information about animals. Types of data (columns) depends on animal type, only a few are common. There can be many animal types with different feature sets (one type can implement one or more feature sets).

What I want to achieve is:

1. to have a possibility to create new animal types, based on existing feature sets without modifying data structure later

2. to have a possibility to make simple selects of all data about animal with known animal_id or all animals with known type

3. to have a data control (to make sure that inserting data e.g. about flying_animal wouldn't be allowed for animal type 'Dog')

4. to Keep It as Simple and Stupid as possible (of course now I can write some stored procedures and triggers "before insert" to make sure that all data is correct, but maybe there is more elegant way?)

Existing data structure:

Table: animals

animal_id (Primary Key)

animal_name

animal_age

animal_type (Foreign Key from animal_types table)

Example data row:

1, 'Rex', 3, 2

Table: animal_types

animal_type_id : int (Primary Key)

animal_type_name : char

-- Here are feature sets:

house_animal : boolean

flying_animal : boolean

wild_animal : boolean

Example data rows:

1, 'Wild parrots', false, true, true

2, 'Dogs', true, false, false

Every feature set is now implemented as table, e.g.:

Table: house_animal

animal_id (Foreign Key from animals table)

favorite_food

last_vet_visit_date

Table: flying animal

animal_id (Foreign Key from animals table)

length_of_wings

max_speed

etc.

Current data structure makes things too complicated.

You can ask me why not to make one big table with all possible columns and one dictionary table with animal types. Of course I can, but that makes data control almost impossible, and how to define a new type with only some of the columns allowed..?

And why wouldn't I create one table for every new type? Because I don't want to have e.g. 200 tables with only a few rows in each of them.

Any ideas?

DBMS is PostgreSQL version 9.1.

Answer 1

One way to do it is to not make one table for each different types of animals. Instead you could use a table referencing animal_types that would store the features that each type has. it would be something like this:

Table: animals

animal_id (Primary Key)

animal_name

animal_age

animal_type (Foreign Key from animal_types table)

Example data row:

1, 'Rex', 3, 2

Table: animal_types

animal_type_id : int (Primary Key)

animal_type_name : char

Now the table that will store the allowed features of each type

Table: animal_types_features

animal_type_id : int (Primary Key) (Foreign Key from animal_types table)

animal_type_feature_id : int (Primary Key)

animal_type_feature_name : char

And then a table to store the features of each animal:

Table: animals_features

animal_id (Foreign Key to table animals)

animal_type_id : (Foreign Key from animal_types_features table)

animal_type_feature_id (Foreign Key from animal_types_features table)

animal_feature_value : char

That's a very simple example, one thing that could be added is that animal_type_features could have a column that would store data to indicate what valid data it could be used for each feature. The benefit of this way is to allow the implementation of new types without extra modification of application and/or database.