5

I am using PostgreSQL to store data on a Person. I need to store whether each Person is capable of teaching a Child of each age year, from 0-17. The ages are discrete values, and a Person can have any number of the 18 (assuming 0-17 years old) assigned to their account. This will be used in a booking system, and also needs to form part of the search results.

Initially I considered creating 18 boolean fields, but this seems inefficient. Is there a better way to do it? I understand that Postgres supports JSONB, so this is an option, but I am unsure of the implications.

Is there a better way to store this data?

|improve this question
  • 1
    You invoke the concept of efficiency but don't say what measure you want to maximise nor what the constrained variables are. Please clarify. – Michael Green Apr 17 '16 at 8:31
8

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.

|improve this answer
  • 1
    Holy, that's quite a comprehensive answer. The question abstracts the problem. I also understand SQL somewhat, but thankyou anyway. All I need to store is whether Person582 is capable of teaching a 0 year old, a 1 year old, a 2 year old, et cetera. I suppose I can just use a table of ID and Age, and use a One-to-many relationship of Person to TeachableAge. – Alex Apr 17 '16 at 8:50
  • 1
    @Rothschild Yes, indeed. If your use case does not require any other attributes associated with age, then all you need is relations persons and persons_pupilClasses above (minus the age foreign key on age, obviously). Storage-wise, this takes one tuple's worth of space for every Person-TeachableAge pair (this is still many-to-many though, unless a given TeachableAge can be taught by at most one Person). – Monitor13 Apr 17 '16 at 9:18
2

Initially I considered creating 18 boolean fields, but this seems inefficient.

If by "efficient" we mean storage size, 18 boolean columns are among the most efficient solutions possible. A boolean column occupies 1 byte and requires no alignment padding. If not-applicable ages can be NULL, it's typically less:

There are even slightly more "efficient" ways for storage, but less easy to manage. In particular a bitstring bit(18) for your case or a plain integer (good for up to 32 bit of information).

Detailed discussion of these options:

How to handle bitstrings:

It really depends on the details of your use case. A key question: can there ever be other relevant values than 0 - 17 or is this cut in stone?

|improve this answer
  • This is the type of answer I was expecting, thanks for the leads Erwin. The values should be ages 0 - 17, but I may also want to cut up the 0-1 range, for newborns and babies over 6 months old. How would this affect your solution? I suppose 19 bits could be used? – Alex Apr 17 '16 at 8:52
  • 2
    @Rothschild: For cases where new options can pop up in the future, a classical, fully normalized many-to-many schema may be more convenient, because it is easier to add and remove options (you only add data and don't have to change the schema) and queries don't have to be adapted structurally. For just a couple of more options like you mention, I might still go with one of the shortcut solutions I discussed. – Erwin Brandstetter Apr 17 '16 at 12:14
1

There are two key bits of information you should add to your question to get better answers:

  1. How is the data expected to be queried? How you want to use the data in your application can make a significant difference to how it is best stored.
  2. What business rules govern the range of ages a person can teach? Is it possible that someone can teach completely arbitrary ages such as "2, 3, 5, 15, 16, and 17 year olds but not the others" or is it always a single range (one of 0-17, 2-33, 15-17, and so on)?

Assuming the latter option for point 2 above, storing the minimum and maximum age a person can teach as values in the person row would probably be the best cross-platform option. In this case to find a person who can teach an 8-year-old you can do WHERE lowestCanTeach<=8 AND highestCanTeach>=8. If you are not worried about cross-platform compatibility then postgres supports ranges as specific data types (see the official documentation) which with correct use may make your model easier to implement and/or more efficient.

If you need multiple age ranges then you would need a separate age-range-can-teach table (with columns PersonID/lowestCanTeach/highestCanTeach), but if you are allowing for that then you are probably modeling fixed age ranges (nursery school age, GCSE level ages, and so on) and if so you should reflect that model in your data model by linking people to classes of teaching and associating the age ranges with the teaching type instead of the person directly.

Going further down this rabbit hole you might need to model subjects too: a person may be able to teach only specific subjects (say, maths and physics) at GCSE level (16/17/18), a wider rage of subjects at lower secondary level (ages 13/14/15), and a wider range still to younger groups.

A separate boolean for each age would work of course, but really doesn't feel "clean" unless being able to teach each year is truly a distinct property from being able to teach any of the other years.

|improve this answer
  • Thank you for your response. I have simplified the context for the sake of this question. The ages are discrete values, and a Person can have any number of the 18 (assuming 0-17 years old) assigned to their account. – Alex Apr 16 '16 at 21:43
0

this is an analytic problem, so you should use dimensional design here. Store the data normalized, but use a view or materialized view to give you the columns you want

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.