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

我正在建立一个SQLite数据库，并且不确定如何进行此方案。

我将使用一个真实的示例来说明我需要什么：

我列出了各州许多商店出售的产品。并非每个人都Store出售某项特定商品Product，而那些出售某项特定商品的人可能只能将其出售State。大多数商店出售一个在大多数国家的产品，但不是全部。

例如，假设我要在夏威夷购买吸尘器。Joe's Hardware在18个州销售吸尘器，但在夏威夷不销售。沃尔玛在夏威夷出售吸尘器，但不出售微波炉。汉堡王根本不卖真空吸尘器，但会给我在美国任何地方的Whopper。

因此，如果我在夏威夷寻找真空，那么我应该只能得到沃尔玛。虽然其他商店可能会出售吸尘器，并且可能在夏威夷出售，但它们都不做，沃尔玛却做。

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

很显然，我需要为表Product，Store和State，但我就如何创建一个损失和查询相应的连接表...

例如，如果我查询某个商品Product，我Store将如何确定要在特定商品中出售哪个商品State，同时要记住沃尔玛可能不会在夏威夷出售吸尘器，但他们确实会在夏威夷卖茶？

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

如果您可以显示一些SQL语句（或DDL）来证明这一点，我将非常感激。谢谢！

公认的常用方法是利用一个表，该表具有用于引用产品的列和用于商店的列。这样的表引用表有很多名称，关联表映射表仅举几例。

您希望它们高效，因此请尝试使用一定数量的引用进行引用，而这些引用当然必须唯一地标识其引用的内容。默认情况下，对于SQLite，表具有特殊的列（通常隐藏），该列是唯一的。它是rowid，通常是访问行的最有效方法，因为SQLite在设计时就考虑了这种常见用法。

SQLite允许您为每个表创建一个列，该列是您简单提供该列后跟（通常将其命名为列ID）的rowid的别名INTEGER PRIMARY KEY。

因此，利用这些参考表，将在产品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的商店（通常是区分大小写的），输出将按照productcost的顺序以ASC结束顺序排序，然后是storename，然后是productname：-

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个新表状态和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-在所有状态下均处于存在状态：-

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

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

现在，得克萨斯州的Sky Hook产生了：-

注意这仅涵盖该主题的基础。