针对许多可能属性的数据库设计
我正在构建一个 MySQL 车辆数据库。我想用数据库、颜色、传输、燃料等填充许多不同的可能的车辆属性。
为每组属性创建一个表是否有意义，或者我应该将它们全部放在一个表中，然后命名每列的属性是什么。然后我只是将可能的选择列为行。
对数据库设计有点陌生，想得到一些意见。提前谢谢您了！
I'm building a MySQL database of vehicles. There are many different possible vehicle attributes that I want to be populated by a database, color, transmission, fuel, etc.
Would it make sense to create a table for each set of attributes or should I put them all in one table, then name each column what the attribute is. Then I'd just list the possible choices as rows.
Kinda new to database design and wanted to get some opinions. Thank you ahead of time!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您问题的答案取决于每辆车是否可以附加一种以上“颜色”或“燃料”。
如果是我，我会让基表看起来像这样：
表：车辆
- 列：车辆 ID、名称等。
表：颜色
- 列：colorID、名称、rgbValue
表：燃料
- Cols：燃料ID、名称等。
如果每辆车只能有一种颜色和一种燃料，则将车辆表更改为：
表：车辆
- 列：车辆 ID、名称、颜色 ID、燃料 ID 等。
如果每个车辆可以有 4 种颜色和 3 种燃料等。则不要理会车辆表，并创建类似于以下的“关系”表：
表：vehicle_has_color
- 列：vehicleID、colorID
表：vehicle_has_fuel
- 列：vehicleID、fuelID
希望这能让您了解理想的数据库设计，并为您提供一个良好的起点。请记住，如果您只有 3 种不同的颜色并且它们不会改变，那么直接在车辆表中插入颜色名称并在 PHP 或 HTML 代码中处理颜色的选择可能会更简单。
The answer to your question depends on whether each vehicle can have more than one "color" or "fuel" attached to it.
If it was me, I would make the base tables look like this:
Table: Vehicles
- Cols: vehicleID, name, etc.
Table: Colors
- Cols: colorID, name, rgbValue
Table: Fuel
- Cols: fuelID, name, etc.
If each vehicle can only have one color and one fuel, then change the Vehicle table to be something like:
Table: Vehicles
- Cols: vehicleID, name, colorID, fuelID, etc.
If each vehicle could have 4 colors and 3 fuels, etc. Then leave the Vehicle table alone, and create "relationship" tables similar to these:
Table: vehicle_has_color
- Cols: vehicleID, colorID
Table: vehicle_has_fuel
- Cols: vehicleID, fuelID
Hopefully this gives you an idea of ideal database design, and gives you a good place to start from. Keep in mind that if you only have 3 different colors and they're not going to change, it might be simpler for you just to insert the color name directly in the Vehicle table and handle the selecting of the color in the PHP or HTML code.
当每个实体都有一组固定的属性时，请将其放入一个表中。
当您有不同的属性时，请将它们放在具有以下结构的单独表中：
When you have a fixed set of attributes for each entity, put it in one table.
When you have varying attributes put them in a separate table with this structure:
如果您的数据库需要显示历史变化，那么我会采用建议#2（将它们全部放在一个表中），但如果您只想通过颜色、传输、燃料等来表示车辆，则将属性放在单独的表。因为这样您就与 ID 号建立了关系，并且如果需要更改某个值，您可以在属性表中进行更改，并且它会在各处更新。它的一致性也更好，例如“29 mpg Hwy”与“29 mpg Hwy”。
If your database needs to display historic changes, then I would go with suggestion #2 (put them all in one table), but if you just want to represent vehicles by the color, transmission, fuel, etc, then put the attributes in a separate table. Because then you have relationships with ID numbers and if a value needs to change, you make that change in an attribute's table and it gets updated everywhere. It's also better for consistency e.g. "29 mpg Hwy" vs "29mpg Hwy".
看看这些示例：一个，两个，三个。
Take a look at these SO examples: one, two, three.