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

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

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

CodePudding user response：

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);

CodePudding user response：

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; a new instance of a test is not created every time a student takes it - all students who take a given test take the same test (ie answer the same questions).

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.