Is there a DB/ORM pattern for attributes?

Question

i want to create an object with different key-value as attributes, for example:

animal
    id
    name

attribute
    id
    name

and mapping

animal_attribute
    animal_id
    attribute_id

so i can have a entry "duck", which has multiple attribute "flying", "swimming", etc. Each attribute type would have its own table defining some variables

attribute_flying
    animal_id
    height
    length
    ..

attribute_swimming
    animal_id
    depth
    ..

is there a better way to do this? Also how would the object layout work in programming (python)?

Answer 1

You have several alternatives. If you have not very deep hierarchy of objects and just several attributes, then you can create one table and add columns for every attribute you need to support

Other way is to create table for each object and map each attribute to different column.

Approach you want to use is not very good due to the following issues:

1. Hard to check if all required attibutes are existing for the animal.
2. Hard to load all attributes for the animal. (actually, if you want to load several animals in one query, then you stuck)
3. It is hard to use different values for attributes.
4. It is hard to make aggregate queries.

Actually this is so called Entity-Attribute-Value antipattern, as described in SQL Antipatterns book.

To resolve this antipattern it is required to rethink how you will store your inheritance in database. There are several approaches:

1. table per class hierarchy
2. table per subclass
3. table per concrete class

Exact solution depends on your task, currently it is hard to decide what is the best solution. Possible you should use table per subclass. In this case you will store common attributes in one table and all specific for the animal goes to the additional table.

sqlalchemy supports all three major types of inheritance, read about inheritance configuration in the documentation and choose what is best for your needs.