Is there a DB/ORM pattern for attributes?
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_swimmi开发者_StackOverflow中文版ng
animal_id
depth
..
is there a better way to do this? Also how would the object layout work in programming (python)?
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:
- Hard to check if all required attibutes are existing for the animal.
- Hard to load all attributes for the animal. (actually, if you want to load several animals in one query, then you stuck)
- It is hard to use different values for attributes.
- 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:
- table per class hierarchy
- table per subclass
- 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.
精彩评论