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(dot)klaver(at)aklaver(dot)com