mysql表结构建议? [英] mysql table structure proposal?

问题描述

此表对mysql有好处吗?我希望将来可以灵活处理这种类型的数据存储.通过这种表结构，您不能使用PRIMARY KEY而是使用索引...

我应该将表格的格式更改为具有标题-主键，宽度，长度，空间，耦合...

ID_NUM  Param   Value
1   Width   5e-081
1   Length  12
1   Space   5e-084
1   Coupling    1.511
1   Metal Layer     M3-0
2   Width   5e-082
2   Length  1.38e-061
2   Space   5e-081
2   Coupling    1.5
2   Metal Layer     M310

解决方案

不，对于关系数据库这是一个糟糕的设计.这是 Entity-Attribute-Value 的示例一个>设计.它很灵活，但是违反了关系数据库的大多数规则.

在进入EAV设计作为灵活数据库解决方案之前，请阅读以下故事:坏的CaRMa .

更具体地说，EAV的一些问题包括:

• 如果不查询任何ID_NUM，您将不知道它们存在哪些属性.
• 您不能使任何属性为强制性，等效于NOT NULL.
• 您不能使用数据库约束.
• 您不能使用SQL数据类型. value列必须为长VARCHAR.
• 尤其是在MySQL中，每个VARCHAR都存储在其自己的数据页上，因此非常浪费.

使用EAV设计时，查询也非常复杂. Magento是一个开放源代码的电子商务平台，广泛使用EAV，许多用户表示，是否需要自定义报告非常缓慢且难以查询.

要保持关系，应将每个不同的属性存储在其自己的列中，并使用其自己的名称和适当的数据类型.

我在演示文稿实用对象- SQL中的面向模型和我的博客文章 EAV失败 ，以及在我的书中， SQL反模式:避免数据库编程的陷阱. /p>

is this table any good for mysql? I wanted to make it flexible in the future for this type of data storage. With this table structure, you can't use a PRIMARY KEY but an index ...

Should I change the format of the table to have headers - Primary Key, Width, Length, Space, Coupling ...

ID_NUM  Param   Value
1   Width   5e-081
1   Length  12
1   Space   5e-084
1   Coupling    1.511
1   Metal Layer     M3-0
2   Width   5e-082
2   Length  1.38e-061
2   Space   5e-081
2   Coupling    1.5
2   Metal Layer     M310

解决方案

No, this is a bad design for a relational database. This is an example of the Entity-Attribute-Value design. It's flexible, but it breaks most rules of what it means to be a relational database.

Before you descend into the EAV design as a solution for a flexible database, read this story: Bad CaRMa.

More specifically, some of the problems with EAV include:

• You don't know what attributes exist for any given ID_NUM without querying for them.
• You can't make any attribute mandatory, the equivalent of NOT NULL.
• You can't use database constraints.
• You can't use SQL data types; the value column must be a long VARCHAR.
• Particularly in MySQL, each VARCHAR is stored on its own data page, so this is very wasteful.

Queries are also incredibly complex when you use the EAV design. Magento, an open-source ecommerce platform, uses EAV extensively, and many users say it's very slow and hard to query if you need custom reports.

To be relational, you should store each different attribute in its own column, with its own name and an appropriate datatype.

I have written more about EAV in my presentation Practical Object-Oriented Models in SQL and in my blog post EAV FAIL, and in my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

这篇关于mysql表结构建议?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！