Is there a way to generate composite key efficiently in a PostgreSQL database?

Question

Suppose I have a student table with id as primary key, which holds information about all students.

id | name
---+------
 1 | aaron
 2 | bob

In addition there is a table where id and tid form a composite key, which holds the scores for each test.

id | tid | score 
---| --- | -----

Note: Different students have different tests with different numbers and no correlation. tid does not represent a specific test at all, but for a student the test serial number. id=1 and id=2, if tid=1, does not mean it is the same test.

There are two ways to generate tid, one is globally unique and increases by 1 for each record inserted, e.g.

id | tid | score 
-- | --- | -----  
 1 |  1  | 99
 1 |  2  | 98
 2 |  3  | 97
 2 |  4  | 96

The other is unique within a specific id, and different ids can have the same tid take value, for example

id | tid | score 
-- | --- | -----  
 1 |  1  | 99
 1 |  2  | 98
 2 |  1  | 97
 2 |  2  | 96

In the previous way, a student with id=2 could probably guess how many tests roughly the whole school went through in between based on his tid change. Since the tid of each student changes globally, this is something I don't want. Of course I could consider using a non-repeating use of random numbers or scheme. But I would prefer a slightly more compact incremental integer to describe it.

For the latter, is there a more efficient and simple way to implement it?

Have a `test` table that creates a row for each unique test and reference that via a FK in the `score` table. — Adrian Klaver, Jul 23 '22 at 15:22
In a relational model "the specific value of a key should not convey any information about the row". That means that for your app design it shouldn't matter if the values are (1, 2, 3, 4), (1, 2, 1, 2), or even (1, 7, 112, 501). Following this premise the first option seems more adequate, but keep in mind that numbers may not be strictly serial. — The Impaler, Jul 23 '22 at 17:22

Adrian Klaver · Accepted Answer · 2022-07-24T16:13:45.960

0

Option 1.

create table student(id integer PRIMARY KEY, name varchar);
create table test(tid integer PRIMARY KEY, name varchar, test_date date);
create table test_score(sid integer, tid integer references test, score integer, PRIMARY KEY(sid, tid);

UPDATE

Option 2.

Create an BEFORE INSERT trigger that uses a function that does roughly:

CREATE OR REPLACE FUNCTION tid_incr()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE 
   max_ct integer;
BEGIN
   SELECT INTO max_ct max(tid) FROM score WHERE id = NEW.id;
   NEW.tid = max_ct + 1;
RETURN NEW;
END;
$function$

edited Jul 24 '22 at 16:13

answered Jul 23 '22 at 15:30

Adrian Klaver

15,886
2
17
28

My description was not very clear and may have misled you, I have now revised the question and added some instructions. You can take another look at it. – progquester Jul 23 '22 at 16:11
@progquester. But `tid` refers to something. It represents some test a student was given and received a score on. To me that means the general form of my answer still holds. – Adrian Klaver Jul 23 '22 at 16:42
Because my requirements were not described clearly, I have now added some new descriptions, can you take a look at the problem again? – progquester Jul 24 '22 at 09:39
See my update with option 2. – Adrian Klaver Jul 24 '22 at 16:13
To be honest, I don't really want to use triggers for this. But if there is no other better and easier way, I'll take your solution as the answer. Thank you very much. – progquester Jul 25 '22 at 01:43

Bohemian · Answer 2 · 2022-07-23T23:06:09.677

-1

The correct design is option 2, because tid should refer (ie be a foreign key) to the test (ie a collection of questions) being taken (whether the test is custom for each student or not).

If students can take the same test more than once, add a date column (or timestamp if multiple attempts may be made of the same day) to distinguish the results of repeat attempts at the same test.

edited Jul 23 '22 at 23:06

answered Jul 23 '22 at 15:27

Bohemian

412,405
93
575
722

My description was not very clear and may have misled you, I have now revised the question and added some instructions. You can take another look at it. – progquester Jul 23 '22 at 16:11
@progquester it doesn't change my answer if tests are unique for a student or shared. tid is a (surrogate) key, which should **not** convey any meaning. I've changed the wording slightly. – Bohemian Jul 23 '22 at 23:04
@Bhemian, Because my requirements were not described clearly, I have now added some new descriptions, can you take a look at the problem again? – progquester Jul 24 '22 at 09:39

Is there a way to generate composite key efficiently in a PostgreSQL database?

2 Answers2