Search code examples
sqldatabase-designforeign-keysmany-to-manycreate-table

SQL table definition for 1-N-M situation


My situation is very complex, but I can simplify my problem to the following situation: Assume a Kindergarden with 3 tables: "kids", "corner" and "day". Each "day", any number of "kids" can play in one of the "corner"s.
The next day, the kids can chose another corner to play. So for each day, the kids and the corners must be uniqe.

Here's an example DB to copy when fiddling:

    CREATE TABLE kids (
      id INT,
      name TEXT
    );
    INSERT INTO kids VALUES (1, "Paul");
    INSERT INTO kids VALUES (2, "Mary");
    INSERT INTO kids VALUES (3, "Tom");
    INSERT INTO kids VALUES (2, "Gina");

    CREATE TABLE corner (
      id INT,
      name TEXT
    );
    INSERT INTO corner VALUES (1, "Kitchen");
    INSERT INTO corner VALUES (2, "Cars");

    CREATE TABLE days (
      id  INT,
      day DATE
    );

Solution

  • Basically, you would need another table to represent the relationship between the three entities:

    create table plays (
        kid_id    int references kids(id),
        corner_id int references corner(id),
        day_id    int references days(id),
        primary key (kid_id, day_id)
    );
    

    Each column is a foreign key to the corresponding referential table. The primary key ensures that a given kid plays in only one corner per day.