如何在数据库中表示实体的许多相似属性? [英] How to represent many similar attributes of an entity in a database?
本文介绍了如何在数据库中表示实体的许多相似属性?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
- 传输(手动/自动)
- 燃油(汽油/柴油/生物乙醇/电动)
- 车身风格(轿跑/轿车/可转换/...)
- 空调(无/简单/双区)
- 外部颜色(黑色/白色/灰色/蓝色/绿色/...)
- / white / gray / blue / green /...)
- 等。
这些属性的列表在将来可能会发生变化。在数据库中建模的最佳方法是什么?我可以想到以下选项,但不能真正地决定:
- 使用
车中的字段
具有枚举值的表
- 稍后添加更多列,可能是最快的
- 使用引用查找表的外键
car
表中的字段
- 难以添加更多colums later稍微慢一些
- 为存储可能值的每个属性创建单独的表,另一个表用于存储汽车和属性值
- 稍后可以添加更多可能的值,甚至更慢,似乎太复杂
解决方案
根据查询的数量和数据库的大小,您可以:
- 创建宽表
- 创建一个attibutes表和一个car_attributes表,其中:cars - > car_attributes - > attributes
-
#1会因为较少的连接而使更快,更简单的查询,但#2更灵活
Let's say I'm building a website about cars. The car entity has a lot of enum-like attributes:
- transmission (manual/automatic)
- fuel (gasoline/diesel/bioethanol/electric)
- body style (coupe/sedan/convertible/...)
- air conditioning (none/simple/dual-zone)
- exterior color (black/white/gray/blue/green/...)
- interior color (black/white/gray/blue/green/...)
- etc.
The list of these attributes is likely to change in the future. What is the optimal way to model them in the database? I can think of the following options but can't really decide:
- use fields in the
car
table with enum values- hard to add more columns later, probably the fastest
- use fields in the
car
table that are foreign keys referencing a lookup table- hard to add more colums later, somewhat slower
- create separate tables for each of those attributes that store the possible values and another table to store the connection between the car and the attribute value
- easy to add more possible values later, even slower, seems to be too complicated
解决方案
Depending upon the number of queries and size of the databases you could either:
- Make wide tables
- Make an attibutes table and a car_attributes table where: cars -> car_attributes -> attributes
#1 will make faster, easier queries due to less joins, but #2 is more flexible
这篇关于如何在数据库中表示实体的许多相似属性?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文