SQL one-to-many

Question

I am trying to build an SQL schema for a system where we have channels, each with an id, and one or more fixtures. I am having difficulty finding a way to implement this one-to-many mapping. (i.e. One channel to many fixtures). I am using the H2 database engine.

I cannot have a table :

 id | fixture
----|----------
  1 |    1
  1 |    2
  2 |    3

CREATE TABLE channel(
  id INT NOT NULL PRIMARY KEY,
  fixture INT NOT NULL
  );

... as the PRIMARY KEY id must be UNIQUE.

Similarly, I cannot map as follows:

CREATE TABLE channel(
  id INT NOT NULL PRIMARY KEY,
  f_set INT NOT NULL REFERENCES fixtures(f_set)
  );

CREATE TABLE fixtures(
  id INT NOT NULL PRIMARY KEY,
  f_set INT NOT NULL
  );

... as this required f_set to be UNIQUE

I am currently implementing it as follows:

CREATE TABLE channel(
  id INT NOT NULL PRIMARY KEY,
  f_set INT NOT NULL REFERENCES fixture_set(id)
  );

CREATE TABLE fixtures(
  id INT NOT NULL PRIMARY KEY,
  f_set INT NOT NULL REFERENCES fixture_set(id)
  );

CREATE TABLE fixture_set(
  id INT NOT NULL PRIMARY KEY
  );

... but this means that we can have a channel with a fixture_set which does not have any assigned fixtures (Not ideal).

I was wondering if you had any suggestions for how i may approach this (Or where my understanding is wrong). Thanks

Answer 1

You can add a CONSTRAINT just to check it. Sorry for not pasting a snippet... I don't know anything about H2 specifics.

Or you could also avoid the fixture-set concept at all. Then you would just need:

• channel table, with just the id (plus other fields not involved on that matter, of course)
• a channelfixtures table, with channelId and fixtureId. Primary key would be composed of (channelId, fixtureId)
• a fixture table, only if you need it.

Answer 2

"One-to-many" means that many items (may) reference one item. If it's one channel to many fixtures, then fixtures should reference channels, not the other way round, which means the reference column should be in the fixtures table:

CREATE TABLE channel(
  id INT NOT NULL PRIMARY KEY
  );

CREATE TABLE fixtures(
  id INT NOT NULL PRIMARY KEY,
  channel_id INT NOT NULL FOREIGN KEY REFERENCES channel (id)
  );