Many to Many

• A products table and a suppliers table – Products may have 0 to many suppliers, and suppliers can supply 0 to many products.
• A 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 among 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 offers 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),
  FOREIGN KEY (product_id) REFERENCES products (id),
  FOREIGN KEY (supplier_id) REFERENCES suppliers (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.

Let's rethink our user ↔ country relationship. Originally, each user had a single country_code field, but many users have dual citizenship!

If we just gave the countries table a user_id (a one-to-many relationship), we would have duplicate country records. If two users are associated with the United States, we'd create two "United States" countries records.

It is better if each country only has a single record. That way, when a country changes its metadata, we only have to update one record. Because a user can have many countries, and a country can have many users, this is a many-to-many relationship.

Use a joining table to link users and countries.

1. Remove the country_code field from the users table.
2. Create a new table called countries. It should have 3 fields:
   • id: an integer primary key
   • country_code: TEXT
   • name: TEXT
3. Create a new table called users_countries. It should have two fields:
   1. country_id
   2. user_id
4. Add a "unique together" constraint on those ID fields.
5. Add foreign keys for those ID fields so that each one references its respective ID in the users or countries table.