How to implement many-to-many-to-many database relationship?

Question

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!

Answer 1

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.