表和n * n(n ^ 2)* 3表在mysql中

A while back I posted a different question regarding column order. While this question does not relate to column order, I was suggested to make my table differently from how I was making it.

不久前，我发布了另一个关于专栏顺序的问题。虽然这个问题与列顺序无关，但我被建议将我的表与我的制作方式不同。

Lets say I am selling 100 products. Some of these products are compatible with each other, some are not. Some have not been tested yet (I did not mention this part in my last question).

假设我销售100种产品。有些产品彼此兼容，有些则不然。有些还没有经过测试(我在上一个问题中没有提到这一部分)。

Would I be better off making a mySQL table like:

我可以做一个mySQL表，比如:

NAME         PRODUCT1     PRODUCT2     PRODUCT3     PRODUCT4 ....
product1     yes          no           maybe        yes
product2     maybe        yes          no           no
product3     maybe        yes          no           no
product4     maybe        yes          no           no
...

or making the table like:

或者把桌子做成:

FIRST       SECOND      COMPATIBLE?
Product1    Product1    Yes
Product1    Product2    Yes
Product1    Product3    No
Product1    Product4    Maybe
Product2    Product1    Maybe
Product2    Product2    Maybe
Product2    Product3    No
Product2    Product4    Maybe
Product3    Product1    Yes
Product3    Product2    Yes
Product3    Product3    No
Product3    Product4    Yes
Product4    Product1    Yes
Product4    Product2    No
Product4    Product3    No
Product4    Product4    Maybe

I was told that the second method would be better, but I failed to mention that there was also the "maybe" option (and not purely yes/no), meaning the third column would have to be added to the second table.

有人告诉我，第二种方法会更好，但我没有提到还有“可能”选项(并非完全是“是/否”)，这意味着必须将第三列添加到第二个表中。

As an inexperienced mySQL'er, I ask, which table would be more efficient, more maintainable, and which would you recommend?

作为一个没有经验的mySQL用户，我想问，哪个表更有效、更易于维护，您推荐哪个表?

3 个解决方案

#1

The second option is still better (even with the third column, which is no problem), because it allows you to easily add new product types without modifying the tables. (The technical term for this is that the schema is better"normalized"). This is much more maintainable. In addition, it means you can join across this table much more easily, or do queries to answer "which is the lowest-cost product which product 1 is compatible with" which would be very hard to do with the first table.

第二个选项仍然更好(即使是第三列，也没有问题)，因为它允许您在不修改表的情况下轻松添加新产品类型。(技术术语是模式是更好的“规范化”)。这更易于维护。此外，这意味着您可以更容易地跨该表进行连接，或者执行查询以回答“哪个产品是与产品1兼容的成本最低的产品”，这对于第一个表来说是非常困难的。

#2

Stick with the second option. If you ever add or remove products you only affect some rows. If you use the first option and add/remove products, you are changing the structure of the table.

坚持第二个选择。如果添加或删除产品，只会影响某些行。如果您使用第一个选项并添加/删除产品，那么您正在更改表的结构。

#3

-1

There is a third option - a "semi-normalized" form. In this case, you'd have a structure something like this:

还有第三种选择——“半规范化”形式。在这种情况下，你会有这样的结构:

Product  DefinitelyCompatible  MaybeCompatible
----------------------------------------------
 1        '2, 3, 4'             '5'
 2        '1, 4'                '3'
 3        '1'                   '3, 4'
 4        '1, 2'                '3'
 5        ''                    '1'

Where the first field is the product ID, and the second and third fields are strings that contain lists of IDs. Using this structure, you can use the FIND_IN_SET() MySQL function to search through the lists, or if you want to look up a given product's compatibilities, you only need fetch one row and then split the strings yourself.

第一个字段是产品ID，第二个和第三个字段是包含ID列表的字符串。使用这种结构，您可以使用FIND_IN_SET() MySQL函数搜索列表，或者如果您想查找给定产品的兼容性，您只需要获取一行，然后自己分割字符串。

This allows you to have only as many rows as you have products (in a Nx3 table), while also minimizing the number of rows you need to alter to add a new product (or update an existing one).

这允许您仅拥有产品(在Nx3表中)的行数，同时最小化添加新产品(或更新现有产品)所需修改的行数。

A further note on performance - assuming that the relationships are symmetric (if a -compat-> b, then b -compat-> a), you don't even need to use FIND_IN_SET() - you can just fetch whichever object (a or b) you need to get the lists for (if you need everything that's compatible with 23, then you fetch 23's row; if you want to see if 4 is compatible with 5, you can fetch either one's row and see if the other appears in it). FIND_IN_SET() would then only be necessary if the check is a portion of a subquery, rather than in your code.

进一步注意对性能——假设的关系是对称的(如果一个兼容- > b,那么b兼容- >一个),您甚至不需要使用FIND_IN_SET()——你可以拿任何一个对象(A或b)你需要的列表(如果你需要所有兼容的23岁,那你卖23行;如果您想查看4是否与5兼容，您可以获取其中的任何一行，并查看其中是否出现了另一行)。如果检查是子查询的一部分，而不是在代码中，那么FIND_IN_SET()将是必需的。

#1

#2