A many-to-many relationship occurs when multiple records in one table can be related to multiple records in another table.
products table and a suppliers table - Products may have 0 to many suppliers, and suppliers can supply 0 to many products.classes table and a students table - Students can take potentially many classes and classes can have many students enrolled.Joining tables help define many-to-many relationships between data in a database. As an example when defining the relationship above between products and suppliers, we would define a joining table called products_suppliers that contains the primary keys from the tables to be joined.
Then, when we want to see if a supplier supplies a specific product, we can look in the joining table to see if the ids share a row.
When enforcing specific schema constraints we may need to enforce the UNIQUE constraint across two different fields.
CREATE TABLE product_suppliers (
product_id INTEGER,
supplier_id INTEGER,
UNIQUE(product_id, supplier_id)
);
This lets multiple rows share the same product_id or supplier_id, but it prevents any two rows from having both the same product_id and supplier_id.
We have another issue with our current user<->country relationship! In the current schema, a user can have many countries associated with it, but there are duplicate records! If two users are associated with the United States, we're creating two countries records.
It would be better if each country only had a single record. That way, when a country changes its metadata (for example, it decides to rename itself) we only have to update one record.
Use a joining table to deduplicate country data.