如何实现多对多数据库关系? [英] How to implement many-to-many-to-many database relationship?
问题描述
我正在构建SQLite数据库，并且不确定如何进行此方案.
我将使用一个真实的例子来说明我需要什么:
我有一个清单，列出了各州许多商店出售的产品.并非每个Store都出售一个特定的Product，而那些出售的Product只能在一个State或另一个State中出售.大多数商店在大多数州都出售 a 产品，但不是全部.
例如，假设我要在夏威夷购买吸尘器. Joe's Hardware 在18个州销售吸尘器，但在夏威夷不销售. Walmart 在夏威夷销售吸尘器，但不出售微波炉. 汉堡王根本不出售吸尘器，但是会给我一个在美国任何地方的购物者".
因此，如果我在夏威夷寻找真空吸尘器，结果应该只得到 Walmart .虽然其他商店可能会出售吸尘器，并可能在夏威夷出售，但它们都不做，沃尔玛却做.
如何有效地在关系数据库中创建这种类型的关系(具体来说，我目前正在使用SQLite，但将来需要能够转换为MySQL).
很明显，我需要用于Product，Store和State的表，但是我对如何创建和查询合适的联接表感到迷茫...
例如，如果我查询某个Product，我将如何确定哪个Store会在特定的State中出售它，请记住 Walmart 可能不会出售真空吸尘器在夏威夷，但是他们在那里卖茶吗?
我了解RD中1:1、1:n和M:n关系的基本知识，但是我不确定在存在多对多情况的情况下如何处理这种复杂性. /p>
如果您可以显示一些SQL语句(或DDL)来证明这一点，我将不胜感激.谢谢！
一种普遍接受的方法是利用一个表，该表具有用于引用产品的列和用于商店的另一列.这样的表引用表有很多名称，而关联表映射表就是其中的一些名称.
您希望它们有效，因此请尝试使用一定数量的引用进行引用，这些数字当然必须唯一地标识其引用的内容.默认情况下，对于SQLite，表具有特殊的列(通常隐藏)，该列是唯一的.这是 rowid ，通常是访问行的最有效方法，因为SQLite在设计时就考虑到了这种常见用法.
SQLite允许您为每个表创建一个列，该列是 rowid 的别名，您只需简单地提供该列，后跟INTEGER PRIMARY KEY，并且通常将其命名为列ID.
因此，利用这些引用，参考表将在产品ID和产品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 (通常区分大小写)的商店，这些输出根据产品成本在 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个新表状态和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)
;
现在，得克萨斯州的天空挂钩"的结果是:-
- 注意，这仅涵盖了该主题的基础.
I am building a SQLite database and am not sure how to proceed with this scenario.
I'll use a real-world example to explain what I need:
I have a list products that are sold by many stores in various states. Not every Store sells a particular Product at all, and those that do, may only sell it in one State or another. Most stores sell a product in most states, but not all.
For example, let's say I am trying to buy a vacuum cleaner in Hawaii. Joe's Hardware sells vacuums in 18 states, but not in Hawaii. Walmart sells vacuums in Hawaii, but not microwaves. Burger King does not sell vacuums at all, but will give me a Whopper anywhere in the US.
So if I am in Hawaii and search for a vacuum, I should only get Walmart as a result. While other stores may sell vacuums, and may sell in Hawaii, they don't do both but Walmart does.
How do I efficiently create this type of relationship in a relational database (specifically, I am currently using SQLite, but need to be able to convert to MySQL in the future).
Obviously, I would need tables for Product, Store, and State, but I am at a loss on how to create and query the appropriate join tables...
If I, for example, query a certain Product, how would I determine which Store would sell it in a particular State, keeping in mind that Walmart may not sell vacuums in Hawaii, but they do sell tea there?
I understand the basics of 1:1, 1:n, and M:n relationships in RD, but I am not sure how to handle this complexity where there is a many-to-many-to-many situation.
If you could show some SQL statements (or DDL) that demonstrates this, I would be very grateful. Thank you!
An accepted and common way is the utilisation of a table that has a column for referencing the product and another for the store. There's many names for such a table reference table, associative table mapping table to name some.
You want these to be efficient so therefore try to reference by a number which of course has to uniquely identify what it is referencing. With SQLite by default a table has a special column, normally hidden, that is such a unique number. It's the rowid and is typically the most efficient way of accessing rows as SQLite has been designed this common usage in mind.
SQLite allows you to create a column per table that is an alias of the rowid you simple provide the column followed by INTEGER PRIMARY KEY and typically you'd name the column id.
So utilising these the reference table would have a column for the product's id and another for the store's id catering for every combination of product/store.
As an example three tables are created (stores products and a reference/mapping table) the former being populated using :-
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')
;
The resultant tables being :-
- _product_store_relationships would be empty
Placing products into stores (for example) could be done using :-
-- 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
;
The _product_store_relationships would then be :-
A query such as the following would list the products in stores sorted by store and then product :-
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
;
The resultant output being :-
This query will only list stores that have a product name that contains an s or S (as like is typically case sensitive) the output being sorted according to productcost in ASCending order, then storename, then 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
;
Output :-
Expanding the above to consider states.
2 new tables states and store_state_reference
- Although no real need for a reference table (a store would only be in one state unless you consider a chain of stores to be a store, in which case this would also cope)
The SQL could be :-
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)
;
If the following query were run :-
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'
;
The output would be :-
Without the WHERE clause :-
make Stores-R-Them have a presence in all states :-
The following would make Stores-R-Them have a presence in all states :-
INSERT INTO _store_state_references VALUES
(2,1),(2,2),(2,3),(2,4)
;
Now the Sky Hook's in Texas results in :-
- Note This just covers the basics of the topic.
这篇关于如何实现多对多数据库关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
