database - 如何实现多对多对多的数据库关系？

Question

我正在构建一个 SQLite 数据库，但不确定如何处理这种情况。

我将使用一个真实世界的例子来解释我需要什么:

我有一个产品 list ，各个州的许多商店都出售这些产品。并非每个 Store 都销售特定的 Product，而那些销售的商店可能只在一个 State 或另一个销售。大多数商店在大多数州销售一种产品，但不是全部。

例如，假设我想在夏威夷购买吸尘器。 Joe's Hardware 在 18 个州销售吸尘器，但不在夏威夷销售。 Walmart 在夏威夷销售吸尘器，但不销售微波炉。 Burger King 根本不卖真空吸尘器，但在美国任何地方都会给我一个 Whopper。

因此，如果我在夏威夷寻找真空吸尘器，我应该只会得到 Walmart 作为结果。虽然其他商店可能销售真空吸尘器，也可能在夏威夷销售，但它们不会同时销售，但沃尔玛会销售。

我如何在关系数据库中有效地创建这种类型的关系(具体来说，我目前使用的是 SQLite，但将来需要能够转换为 MySQL)。

显然，我需要用于Product、Store 和State 的表，但我不知道如何创建并查询适当的连接表...

例如，如果我查询某个 Product，我将如何确定哪个 Store 会在特定的 State 销售它，保持请记住，Walmart 在夏威夷可能不卖吸尘器，但他们在那里卖茶？

我了解 RD 中 1:1、1:n 和 M:n 关系的基础知识，但我不确定如何在存在多对多对多情况下处理这种复杂性。

如果您能展示一些证明这一点的 SQL 语句(或 DDL)，我将不胜感激。谢谢!

Answer 1

一种公认且常见的方法是使用一个表，其中有一列用于引用产品，另一列用于引用商店。这样的表引用表、关联表映射表的名称有很多种。

您希望这些是高效的，因此请尝试通过数字进行引用，当然必须唯一标识它所引用的内容。在 SQLite 中，默认情况下，一个表有一个特殊的列，通常是隐藏的，这是一个唯一的数字。它是 rowid，通常是访问行的最有效方式，因为 SQLite 在设计时就考虑到了这种常见用法。

SQLite 允许您为每个表创建一个列，该列是 rowid 的别名，您只需提供后跟 INTEGER PRIMARY KEY 的列，通常您会命名列 ID。

因此，利用这些，引用表将有一列用于产品 ID，另一列用于商店 ID，以满足产品/商店的每种组合。

作为示例，创建了三个表(存储产品和引用/映射表)，前者使用以下内容填充:-

CREATE TABLE IF NOT EXISTS _products(id INTEGER PRIMARY KEY, productname TEXT, productcost REAL);
CREATE TABLE IF NOT EXISTS _stores (id INTEGER PRIMARY KEY, storename TEXT);
CREATE TABLE IF NOT EXISTS _product_store_relationships (storereference INTEGER, productreference INTEGER);
INSERT INTO _products (productname,productcost) VALUES
    ('thingummy',25.30),
    ('Sky Hook',56.90),
    ('Tartan Paint',100.34),
    ('Spirit Level Bubbles - Large', 10.43),
    ('Spirit Level bubbles - Small',7.77)
;
INSERT INTO _stores (storename) VALUES
    ('Acme'),
    ('Shops-R-Them'),
    ('Harrods'),
    ('X-Mart')
;

结果表是:-

• _product_store_relationships 将为空

将产品放入商店(例如)可以使用:-

-- Build some relationships/references/mappings
INSERT INTO  _product_store_relationships VALUES
    (2,2), -- Sky Hooks are in Shops-R-Them
    (2,4), -- Sky Hooks in x-Mart
    (1,3), -- thingummys in Harrods
    (1,1), -- and Acme
    (1,2), -- and Shops-R-Them
    (4,4), -- Spirit Level Bubbles Large in X-Mart
    (5,4), -- Spiirit Level Bubble Small in X-Mart
    (3,3) -- Tartn paint in Harrods
;

_product_store_relationships 将是:-

如下查询将列出商店中按商店排序的产品，然后按产品排序:-

SELECT storename, productname, productcost FROM _stores 
JOIN _product_store_relationships ON _stores.id = storereference 
JOIN _products ON _product_store_relationships.productreference = _products.id
ORDER BY storename, productname
;

结果输出为:-

此查询将仅列出产品名称包含 s 或 S(因为 like 通常区分大小写)的商店，输出根据 productcost in ASC结束订单，然后是商店名称，然后是产品名称:-

SELECT storename, productname, productcost FROM _stores 
JOIN _product_store_relationships ON _stores.id = storereference 
JOIN _products ON _product_store_relationships.productreference = _products.id
WHERE productname LIKE '%s%'
ORDER BY productcost,storename, productname 
;

输出:-

扩展以上内容以考虑状态。

2 个新表 states 和 store_state_reference

• 尽管实际上不需要引用表(一家商店只会处于一种状态，除非您将链式店视为一家商店，在这种情况下，这也可以应付)

SQL 可以是:-

CREATE TABLE IF NOT EXISTS _states (id INTEGER PRIMARY KEY, statename TEXT);
INSERT INTO _states (statename) VALUES
    ('Texas'),
    ('Ohio'),
    ('Alabama'),
    ('Queensland'),
    ('New South Wales')
;
CREATE TABLE IF NOT EXISTS _store_state_references (storereference, statereference);
INSERT INTO _store_state_references VALUES
    (1,1),
    (2,5),
    (3,1),
    (4,3)
;

如果运行以下查询:-

SELECT storename,productname,productcost,statename
FROM _stores
JOIN  _store_state_references ON _stores.id = _store_state_references.storereference
JOIN _states ON _store_state_references.statereference =_states.id
JOIN _product_store_relationships ON _stores.id = _product_store_relationships.storereference
JOIN _products ON _product_store_relationships.productreference = _products.id
WHERE statename = 'Texas' AND productname = 'Sky Hook'
;

输出将是:-

没有 WHERE 子句:-

让 Stores-R-Them 在所有州都有存在:-

以下将使 Stores-R-Them 在所有州都有存在:-

INSERT INTO _store_state_references VALUES
   (2,1),(2,2),(2,3),(2,4)
;

现在 Sky Hook 在德克萨斯州的结果是:-

• 注意这只涵盖了主题的基础知识。