Re: Need a referential constraint to a non-unique record - Mailing list pgsql-general

From Adrian Klaver
Subject Re: Need a referential constraint to a non-unique record
Date
Msg-id 3f36f68f-5fbf-74d4-350f-0dcb9f647c76@aklaver.com
Whole thread Raw
In response to Need a referential constraint to a non-unique record  (David Gauthier <davegauthierpg@gmail.com>)
List pgsql-general
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



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: pg_dump (COPY) hanging intermittently
Next
From: Prakash Ramakrishnan
Date:
Subject: Re: patch 11.2 to 11.4