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

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