An associate has created a schema that uses an ENUM() column for the primary key on a lookup table. The table turns a product code "FB" into it's name "Foo Bar".
关联方创建了一个模式，该模式在查找表上使用ENUM（）列作为主键。该表将产品代码“FB”转换为其名称“Foo Bar”。
This primary key is then used as a foreign key elsewhere. And at the moment, the FK is also an ENUM().
然后，该主键在其他地方用作外键。目前，FK也是一个ENUM（）。
I think this is not a good idea. This means that to join these two tables, we end up with four lookups. The two tables, plus the two ENUM(). Am I correct?
我认为这不是一个好主意。这意味着要连接这两个表，我们最终会进行四次查找。这两个表加上两个ENUM（）。我对么？
I'd prefer to have the FKs be CHAR(2) to reduce the lookups. I'd also prefer that the PKs were also CHAR(2) to reduce it completely.
我更喜欢让FK成为CHAR（2）以减少查找次数。我也更喜欢PK也是CHAR（2）来完全减少它。
The benefit of the ENUM()s is to get constraints on the values. I wish there was something like: CHAR(2) ALLOW('FB', 'AB', 'CD') that we could use for both the PK and FK columns.
ENUM（）的好处是获得对值的约束。我希望有类似的东西：CHAR（2）ALLOW（'FB'，'AB'，'CD'）我们可以用于PK和FK列。
What is:
什么是：
- Best Practice
- 最佳实践
- Your preference
- 你的偏好
This concept is used elsewhere too. What if the ENUM()'s values are longer? ENUM('Ding, dong, dell', 'Baa baa black sheep'). Now the ENUM() is useful from a space point-of-view. Should I only care about this if there are several million rows using the values? In which case, the ENUM() saves storage space.
这个概念也在别处使用。如果ENUM（）的值更长怎么办？ ENUM（'Ding，dong，dell'，'Baa baa black sheep'）。现在，ENUM（）从空间的角度来看很有用。如果有几百万行使用这些值，我应该只关心这个吗？在这种情况下，ENUM（）可以节省存储空间。
4 个解决方案
#1
5
ENUM should be used to define a possible range of values for a given field. This also implies that you may have multiple rows which have the same value for this perticular field.
ENUM应该用于定义给定字段的可能值范围。这也意味着您可能有多个行，这些行对于此特定字段具有相同的值。
I would not recommend using an ENUM for a primary key type of foreign key type.
我不建议将ENUM用于主键类型的外键类型。
Using an ENUM for a primary key means that adding a new key would involve modifying the table since the ENUM has to be modified before you can insert a new key.
使用ENUM作为主键意味着添加新密钥将涉及修改表，因为必须先修改ENUM，然后才能插入新密钥。
I am guessing that your associate is trying to limit who can insert a new row and that number of rows is limited. I think that this should be achieved through proper permission settings either at the database level or at the application and not through using an ENUM for the primary key.
我猜你的同事试图限制谁可以插入一个新行，并且该行数是有限的。我认为这应该通过在数据库级别或应用程序上的适当权限设置来实现，而不是通过使用ENUM作为主键。
IMHO, using an ENUM for the primary key type violates the KISS principle.
恕我直言，使用ENUM作为主键类型违反了KISS原则。
#2
4
but when you only trapped with differently 10 or less rows that wont be a problem
但是当你只用不同的10行或更少的行捕获时不会有问题
e.g's
CREATE TABLE `grade`(
`grade` ENUM('A','B','C','D','E','F') PRIMARY KEY,
`description` VARCHAR(50) NOT NULL
)
This table it is more than diffecult to get a DML
这个表获得DML是不同的
#3
2
We've had more discussion about it and here's what we've come up with:
我们已经对它进行了更多的讨论，这就是我们提出的：
Use CHAR(2) everywhere. For both the PK and FK. Then use mysql's foreign key constraints to disallow creating an FK to a row that doesn't exist in the lookup table.
到处使用CHAR（2）。对于PK和FK。然后使用mysql的外键约束禁止在查找表中不存在的行中创建FK。
That way, given the lookup table is L, and two referring tables X and Y, we can join X to Y without any looking up of ENUM()s or table L and can know with certainty that there's a row in L if (when) we need it.
这样，如果查找表是L，并且两个引用表X和Y，我们可以将X连接到Y而不需要查找ENUM（）或表L，并且可以肯定地知道L中有一行（如果） ） 我们需要。
I'm still interested in comments and other thoughts.
我仍然对评论和其他想法感兴趣。
#4
0
Having a lookup table and a enum means you are changing values in two places all the time. Funny... We spent to many years using enums causing issues where we need to recompile to add values. In recent years, we have moved away from enums in many situations an using the values in our lookup tables. The biggest value I like about lookup tables is that you add or change values without needing to compile. Even with millions of rows I would stick to the lookup tables and just be intelligent in your database design
拥有查找表和枚举意味着您始终在两个位置更改值。有趣......我们花了很多年时间使用枚举导致我们需要重新编译以添加值的问题。近年来，在许多情况下，我们已经使用查找表中的值来远离枚举。我喜欢查找表的最大值是您无需编译即可添加或更改值。即使有数百万行，我也会坚持使用查找表，只是在数据库设计中保持智能
#1
5
ENUM should be used to define a possible range of values for a given field. This also implies that you may have multiple rows which have the same value for this perticular field.
ENUM应该用于定义给定字段的可能值范围。这也意味着您可能有多个行，这些行对于此特定字段具有相同的值。
I would not recommend using an ENUM for a primary key type of foreign key type.
我不建议将ENUM用于主键类型的外键类型。
Using an ENUM for a primary key means that adding a new key would involve modifying the table since the ENUM has to be modified before you can insert a new key.
使用ENUM作为主键意味着添加新密钥将涉及修改表，因为必须先修改ENUM，然后才能插入新密钥。
I am guessing that your associate is trying to limit who can insert a new row and that number of rows is limited. I think that this should be achieved through proper permission settings either at the database level or at the application and not through using an ENUM for the primary key.
我猜你的同事试图限制谁可以插入一个新行，并且该行数是有限的。我认为这应该通过在数据库级别或应用程序上的适当权限设置来实现，而不是通过使用ENUM作为主键。
IMHO, using an ENUM for the primary key type violates the KISS principle.
恕我直言，使用ENUM作为主键类型违反了KISS原则。
#2
4
but when you only trapped with differently 10 or less rows that wont be a problem
但是当你只用不同的10行或更少的行捕获时不会有问题
e.g's
CREATE TABLE `grade`(
`grade` ENUM('A','B','C','D','E','F') PRIMARY KEY,
`description` VARCHAR(50) NOT NULL
)
This table it is more than diffecult to get a DML
这个表获得DML是不同的
#3
2
We've had more discussion about it and here's what we've come up with:
我们已经对它进行了更多的讨论，这就是我们提出的：
Use CHAR(2) everywhere. For both the PK and FK. Then use mysql's foreign key constraints to disallow creating an FK to a row that doesn't exist in the lookup table.
到处使用CHAR（2）。对于PK和FK。然后使用mysql的外键约束禁止在查找表中不存在的行中创建FK。
That way, given the lookup table is L, and two referring tables X and Y, we can join X to Y without any looking up of ENUM()s or table L and can know with certainty that there's a row in L if (when) we need it.
这样，如果查找表是L，并且两个引用表X和Y，我们可以将X连接到Y而不需要查找ENUM（）或表L，并且可以肯定地知道L中有一行（如果） ） 我们需要。
I'm still interested in comments and other thoughts.
我仍然对评论和其他想法感兴趣。
#4
0
Having a lookup table and a enum means you are changing values in two places all the time. Funny... We spent to many years using enums causing issues where we need to recompile to add values. In recent years, we have moved away from enums in many situations an using the values in our lookup tables. The biggest value I like about lookup tables is that you add or change values without needing to compile. Even with millions of rows I would stick to the lookup tables and just be intelligent in your database design
拥有查找表和枚举意味着您始终在两个位置更改值。有趣......我们花了很多年时间使用枚举导致我们需要重新编译以添加值的问题。近年来，在许多情况下，我们已经使用查找表中的值来远离枚举。我喜欢查找表的最大值是您无需编译即可添加或更改值。即使有数百万行，我也会坚持使用查找表，只是在数据库设计中保持智能