Thread: Need a referential constraint to a non-unique record

On 6/25/19 2:58 PM, David Gauthier wrote:
> I need to create a constraint on a column of a table such that it's 
> value is found in another table but may not be unique in that other 
> table.  Example...
> 
> Let's say the DB is about students and the grades they got for 4 
> subjects... Math, English, Science, History.  But instead of creating 4 
> records in the "grades" table for every record in the "students" table, 

But you are.

> I storing each unique combination of grades in the "grades" table, those 
> records tied together with a common "id" field...
> 
> grade_id   subject      grade
> 1          math         A
> 1          english      A
> 1          science      A
> 1          history      A
> 2          math         B
> 2          english      A
> 2          science      C
> 2          history      B
> 
> etc...  Each unique combination of the 4 subject/grades gets a new "id" 
> and those 4 records are written to the grates table.

Why not?:

grade_id student_id   subject      grade
1       1          math         A
2          1          english      A
3          1          science      A
4          1          history      A
5          2          math         B
6          2          english      A
7          2          science      C
8          2          history      B

Where grade_id is the PK and student_id is FK to students

> 
> Now, in the "students" table I have a "grad_id" column which points to 
> the set of grades for that student.  The "grade_id" value in the 
> "students" table must also exist in the "grades" table.  But the 
> grade_id value is pointing to 4, not 1 record in the "grades" table.  
> And "grade_id" in the "grades" table can't (obviously) be a PK.
> 
> There are no primary keys in this scenario so I don't think I can set up 
> a traditional primary-foreign key relationship.
> 
> I could do this with a check constraint.  But I want the ER view in the 
> DBeaver tool to recognize the constraint and depict it.
> 
> I suppose I could create a bridge table between the "students" and 
> "grades" table which has only the "grades_id" column as a primary key, 
> and then set up 2 traditional primary/foreign key constraints (one 
> between this new table and "grades", and the other between this new 
> table and "students").  But it's kinda unnecessary and am looking for 
> something more direct, without the bridge.
> 
> Any ideas ?
> psql (9.6.7, server 9.5.2) on linux
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 6/26/19 7:28 AM, David Gauthier wrote:
Ccing list.

> Actually, I'm not storing 4 records in the 'grades' table for each 
> record in the 'students' table.  For example, if student Joe gets 
> straight A's, he points to grades.grade_id = 1.  If student Sue also 
> gets straight A's, she points to grades.grade_id = 1.  I'm reusing the 
> set of records in "grades" for >1 student.  It's not a situation where 
> there's a "student_id" field in the "grades" table which points to a 
> "student_id" (PK) field in the "students" table with a FK relationship.
> 
> This simplistic example is not what's going on in my real application 
> where the equivalent of the "grades" table has hundreds of records and 
> there are thousands of "students".  I don't want to have to duplicate 
> the set of "grades" records over and over again if an existing set can 
> be "reused".

Hmm.

1) So each 'student' has to take all the 'subjects'?
If not then what happens?

2) You have to pre-plan all the possible combinations.
What happens if a 'subject' is added/deleted?

3) Your app has to 'know' what id points to what combination.

I see the above as resistant to change and fragile and as you already 
have determined hard to model. I'm thinking a more traditional layout 
will be better over the long run.


-- 
Adrian Klaver
adrian.klaver@aklaver.com