This question is an extension of another question I asked regarding many-to-many relationships in MySQL.
这个问题是关于MySQL中多对多关系的另一个问题的扩展。
I currently have 3 tables that I need to link with a 4th intermediary table:
我目前有3个表需要与第4个中间表链接:
Stores, Products, and States
商店、产品和状态
My intermediary table, _stores_products_states, combines the id from the other three tables to determine which product is sold by which store and in which state.
我的中间表_stores_products_states组合了来自其他三个表的id，以确定哪个产品是由哪个存储和哪个状态销售的。
Now, as I understand it, I would need to create an entry in _stores_products_states for every possible combination of the three, correct? This would lead to thousands of duplicated values in 1-2 of the columns (though never all 3).
现在，根据我的理解，我需要在_stores_products_states中为这三个元素的每个可能组合创建一个条目，对吗?这将导致1-2列中有数千个重复的值(尽管不是全部3列)。
For example, if Best Guy sells both GI Bros and Darbies in all 50 states, that would be 100 entries just for those two products. If those products are sold by another store, they too would have 100 entries.
例如，如果Best Guy在全美50个州同时销售GI Bros和Darbies，那么仅这两款产品就会有100个条目。如果这些产品被其他商店出售，他们也会有100个条目。
Is this the correct way to implement this kind of relationship?
这是实现这种关系的正确方法吗?
EDIT: This whole setup is basically just to determine the availability of a particular product. A user will search for a product and receive a list of stores that sell that product in their state.
编辑:整个设置基本上就是为了确定特定产品的可用性。用户将搜索一个产品，并接收在其状态下销售该产品的商店列表。
2 个解决方案
#1
1
The 4th table is the way! So if I got it right, your '_stores_products_states' table could even be called sale
第四张桌子就是。因此，如果我做对了，您的“_stores_products_states”表甚至可以称为sales
#2
1
You do not need to create a record for all possible combinations of product, state, and store. You only need to create a record for existing combinations, that is, availability of a product in a store in a state (maybe with things like local price and quantity bolted on).
您不需要为产品、状态和存储的所有可能组合创建一个记录。您只需要为现有的组合创建一个记录，即在一个州的商店中创建产品的可用性(可能包含本地价格和数量)。
You will have to store this information one way or another; a 3-relation link table, especially stored as a clustered MySQL index, would be a pretty standard solution, with good performance characteristics.
你必须以某种方式存储这些信息;一个3关系的链接表，特别是作为一个集群的MySQL索引存储，将是一个非常标准的解决方案，具有良好的性能特征。
One thing I wonder about is why you have stores separate from states. I'd expect a store to be associated with a state. With the 3-relation link table, you'd be able to associate the same store with a product in several different states. Is this what your business domain supposes?
我想知道的一件事是为什么你的商店与州分开。我希望一个商店能与一个州联系在一起。使用3-relation链接表，您可以将同一商店与不同状态的产品关联起来。这是你的业务领域所设想的吗?
#1
1
The 4th table is the way! So if I got it right, your '_stores_products_states' table could even be called sale
第四张桌子就是。因此，如果我做对了，您的“_stores_products_states”表甚至可以称为sales
#2
1
You do not need to create a record for all possible combinations of product, state, and store. You only need to create a record for existing combinations, that is, availability of a product in a store in a state (maybe with things like local price and quantity bolted on).
您不需要为产品、状态和存储的所有可能组合创建一个记录。您只需要为现有的组合创建一个记录，即在一个州的商店中创建产品的可用性(可能包含本地价格和数量)。
You will have to store this information one way or another; a 3-relation link table, especially stored as a clustered MySQL index, would be a pretty standard solution, with good performance characteristics.
你必须以某种方式存储这些信息;一个3关系的链接表，特别是作为一个集群的MySQL索引存储，将是一个非常标准的解决方案，具有良好的性能特征。
One thing I wonder about is why you have stores separate from states. I'd expect a store to be associated with a state. With the 3-relation link table, you'd be able to associate the same store with a product in several different states. Is this what your business domain supposes?
我想知道的一件事是为什么你的商店与州分开。我希望一个商店能与一个州联系在一起。使用3-relation链接表，您可以将同一商店与不同状态的产品关联起来。这是你的业务领域所设想的吗?