The ages are discrete values
OK.
a Person can have any number of the 18 (assuming 0-17 years old) assigned to their account.
So it's a many-to-many relationship?
If so, you just decompose your data into third normal form as usual, expressing the cardinality by means of one extra relation.
Example follows. The SQL dialect is not necessarily PostgreSQL as I do not have an installation handy right now.
Schema
-- persons relation
CREATE TABLE persons (
personId INT NOT NULL PRIMARY KEY,
name TEXT NOT NULL
-- Any other attributes
);
-- classes relation
CREATE TABLE pupilClasses (
age INT NOT NULL PRIMARY KEY,
size ENUM ('small', 'medium', 'large') NOT NULL
);
-- "Tie" relation that expresses the many-to-many cardinality
-- between persons and classes
CREATE TABLE persons_pupilClasses (
personId INT NOT NULL,
age INT NOT NULL,
PRIMARY KEY (personId, age),
FOREIGN KEY (personId) REFERENCES persons (personId)
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (age) REFERENCES pupilClasses (age)
ON UPDATE CASCADE ON DELETE CASCADE
);
Data
-- Let us populate with some data
-- A few classes
INSERT INTO pupilClasses (age, size) VALUES (0, 'small');
INSERT INTO pupilClasses (age, size) VALUES (1, 'small');
INSERT INTO pupilClasses (age, size) VALUES (2, 'small');
INSERT INTO pupilClasses (age, size) VALUES (3, 'small');
INSERT INTO pupilClasses (age, size) VALUES (4, 'small');
INSERT INTO pupilClasses (age, size) VALUES (5, 'small');
INSERT INTO pupilClasses (age, size) VALUES (6, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (7, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (8, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (9, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (10, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (11, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (12, 'large');
INSERT INTO pupilClasses (age, size) VALUES (13, 'large');
INSERT INTO pupilClasses (age, size) VALUES (14, 'large');
INSERT INTO pupilClasses (age, size) VALUES (15, 'large');
INSERT INTO pupilClasses (age, size) VALUES (16, 'large');
INSERT INTO pupilClasses (age, size) VALUES (17, 'large');
-- A few persons
INSERT INTO persons (personId, name) VALUES (666, 'Alice');
INSERT INTO persons (personId, name) VALUES (667, 'Bertrand');
INSERT INTO persons (personId, name) VALUES (668, 'Carlos');
-- Who can teach to whom
-- Alice to 0−3 and 7−8 classes
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 0);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 1);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 2);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 3);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 7);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 8);
-- Bertrand to 7−9, 13, 16−17 classes
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 7);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 8);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 9);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 13);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 16);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 17);
-- Carlos can't teach anyone yet ☹
Logic
Let us get a report on those who can teach any classes
SELECT persons.name, GROUP_CONCAT(pupilClasses.age) classes
FROM persons
INNER JOIN persons_pupilClasses
ON persons.personId = persons_pupilClasses.personId
INNER JOIN pupilClasses
ON pupilClasses.age = persons_pupilClasses.age
GROUP BY persons.personId;
Result:
+----------+----------------+
| name | classes |
+----------+----------------+
| Alice | 0,1,2,3,7,8 |
| Bertrand | 7,8,9,13,16,17 |
+----------+----------------+
Let us get a report of who can teach to whom, if anyone
SELECT persons.name, GROUP_CONCAT(pupilClasses.age) classes
FROM persons
LEFT JOIN persons_pupilClasses
ON persons.personId = persons_pupilClasses.personId
LEFT JOIN pupilClasses
ON pupilClasses.age = persons_pupilClasses.age
GROUP BY persons.personId;
Result:
+----------+----------------+
| name | classes |
+----------+----------------+
| Alice | 0,1,2,3,7,8 |
| Bertrand | 7,8,9,13,16,17 |
| Carlos | NULL |
+----------+----------------+
Who teach class 3?
SELECT persons.name
FROM persons
INNER JOIN persons_pupilClasses
ON persons.personId = persons_pupilClasses.personId
WHERE persons_pupilClasses.age = 3;
Result:
+-------+
| name |
+-------+
| Alice |
+-------+
Who can teach classes 7 or 9?
SELECT DISTINCT persons.name
FROM persons
INNER JOIN persons_pupilClasses
ON persons.personId = persons_pupilClasses.personId
WHERE persons_pupilClasses.age IN (7,9);
Result:
+----------+
| name |
+----------+
| Alice |
| Bertrand |
+----------+
Who can teach large sizes?
SELECT DISTINCT persons.name
FROM persons
INNER JOIN persons_pupilClasses
ON persons.personId = persons_pupilClasses.personId
INNER JOIN pupilClasses
ON pupilClasses.age = persons_pupilClasses.age
WHERE pupilClasses.size = 'large';
Result:
+----------+
| name |
+----------+
| Bertrand |
+----------+
Although you do not mention it in your description, I'm willing to bet there is at least one more important relation on your actual business scenario: pupils. And they probably move from one class to the next. A simple way to represent that relationship would be:
-- pupils relation
CREATE TABLE pupils (
pupilId INT NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
age INT NOT NULL,
FOREIGN KEY (age) REFERENCES pupilClasses (age)
ON UPDATE RESTRICT ON DELETE RESTRICT
);
-- A few pupils
INSERT INTO pupils (pupilId, name, age) VALUES (1313, 'Donald', 7);
INSERT INTO pupils (pupilId, name, age) VALUES (1314, 'Ernest', 7);
INSERT INTO pupils (pupilId, name, age) VALUES (1315, 'Frank', 9);
INSERT INTO pupils (pupilId, name, age) VALUES (1316, 'Gertrude', 0);
INSERT INTO pupils (pupilId, name, age) VALUES (1321, 'Hans', 13);
And some logic examples:
Who teaches whom?
SELECT persons.name teacher, pupils.name pupil
FROM persons
INNER JOIN persons_pupilClasses
ON persons.personId = persons_pupilClasses.personId
INNER JOIN pupils
ON pupils.age = persons_pupilClasses.age;
Result:
+----------+----------+
| teacher | pupil |
+----------+----------+
| Alice | Donald |
| Bertrand | Donald |
| Alice | Ernest |
| Bertrand | Ernest |
| Bertrand | Frank |
| Alice | Gertrude |
| Bertrand | Hans |
+----------+----------+
Who are each person's pupils?
SELECT persons.name teacher, GROUP_CONCAT(pupils.name) pupils
FROM persons
INNER JOIN persons_pupilClasses
ON persons.personId = persons_pupilClasses.personId
INNER JOIN pupils
ON pupils.age = persons_pupilClasses.age
GROUP BY persons.personId;
Result:
+----------+--------------------------+
| teacher | pupils |
+----------+--------------------------+
| Alice | Donald,Ernest,Gertrude |
| Bertrand | Donald,Ernest,Frank,Hans |
+----------+--------------------------+
Who are each pupil's teachers?
SELECT pupils.name pupil, GROUP_CONCAT(persons.name) teachers
FROM persons
INNER JOIN persons_pupilClasses
ON persons.personId = persons_pupilClasses.personId
INNER JOIN pupils
ON pupils.age = persons_pupilClasses.age
GROUP BY pupils.pupilId;
Result:
+----------+----------------+
| pupil | teachers |
+----------+----------------+
| Donald | Alice,Bertrand |
| Ernest | Alice,Bertrand |
| Frank | Bertrand |
| Gertrude | Alice |
| Hans | Bertrand |
+----------+----------------+
From this a couple things should be clear:
age
may be a bit of a misnomer it may not correspond with the actual natural age of pupils attending those classes.
It's probably a good idea to avoid the temptation of treating it as a continuous range. This is why I quoted your statement that these are discrete values.
For the rest, unless I misunderstood, seems a fairly straightforward normalisation problem.
By the way:
Initially I considered creating 18 boolean fields,
Where? In the person
relation?
but this seems inefficient.
It's not inefficient. It's a red herring.
Normalise your design first. Once you've gained experience with the data you will be handling you can start thinking about introducing optimisations (which may involve denormalisation). Pre-emptive optimisation is just one of those terrible ideas though.