Multi-field relationships?

[Douglas J. Steele]

Not everyone uses autonumber fields as the primary key. Anytime you use a
multifield primary key, you're going to need to have multiple foreign key
fields. I can't think of a meaningful example right now, but I've got tables
with 3 and 4 fields as the primary key, which means having 3 or 4 fields in
the relationship.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Just happened to notice in the Relationships permits multiple fields.
In five or six years of heavy Access-ing, I haven't ever run into a reason

why you might want two fields in a parent table tied to two fields in a
child table.

To the contrary, all roads seem to lead to a single (typically AutoNumber)

key for any table whose primary key might be a foreign key elsewhere.

 

[smitty_one_each]

Just happened to notice in the Relationships permits multiple fields.
In five or six years of heavy Access-ing, I haven't ever run into a reason why you might want two fields in a parent table tied to two fields in a child table.
To the contrary, all roads seem to lead to a single (typically AutoNumber) key for any table whose primary key might be a foreign key elsewhere.
Any real-world examples?

 

[TC]

As Douglas said, not everyone uses autonumbers.

tblPerson
PersonID
name, date of birth etc.

tblPersonHoliday
PersonID ( composite )
HolidayDate ( primary key)
approved y/n, etc.

In general, ignoring autonumbers, the primary key of a child table, is a
composite key comprising (a) the primary key field(s) of the parent table,
plus (b) one or more fields from the child table which serve to distinguish
the child entries for the same parent record. So the number of fields in the
PK will increase by at least one, as you go downawards through a
parent/child structure.

If this gets too unweildy (and/or for various other reasons), an autonumber
can make things simpler. But in my experience, I have seldom designed a
non-trivial database that did >not< have at least one table with a composite
primary key.

HTH,
TC

Just happened to notice in the Relationships permits multiple fields.
In five or six years of heavy Access-ing, I haven't ever run into a reason

why you might want two fields in a parent table tied to two fields in a
child table.

To the contrary, all roads seem to lead to a single (typically AutoNumber)

key for any table whose primary key might be a foreign key elsewhere.

 

[Chris Smith]

But in my experience, I have seldom designed a

non-trivial database that did >not< have at least one table with a composite
primary key.

True, true. (BTW, this question has nothing whatsoever to
do with schoolwork)

<example>
tbl_student
student_id
...stuff...

tbl_assignment
assignment_id
assignment_name

tbl_submitted_assignment
student_id \_________composite primary key
assignment_id /
submitted_assignment_grade
submitted_assignment_note
</example>

We might well stop there.
The point I was getting at is that, in a more
sophisticated example, where the many-to-many linkage
modeled by tbl_submitted_assignment becomes a full-on
entity whose primary key would be used as a foreign key,
(at the risk of getting too contrived),

tbl_submitted_assignment_log
<foreign key to tbl_submitted_assignment
goes here>
submitted_assignment_log_date_time
submitted_assignment_log_note

I think it would start to become painful, both in terms of
understandability and performance, if the foreign key to
tbl_submitted_assignment is the original
student_id/assignment_id composite of
tbl_submitted_assignment. With this requirement, I'd drop
the primary constraint on the original
student_id/assignment_id, and leave it as a unique
composite index (so that I preclude multiple submissions of
unique assignments), and go with

<example>
tbl_submitted_assignment
submitted_assignment_id---primary key
student_id \___________,composite index
assignment_id /
submitted_assignment_grade
submitted_assignment_note

tbl_submitted_assignment_log
submitted_assignment_id
submitted_assignment_log_date_time
submitted_assignment_log_note
</example>

Reworking my original question, are there any reasons for

not< taking this approach? Best I can think of is that

you're doing linked tables from SacredCow.mdb, and you
simply lack the leverage to do it.

Best,
Chris

 

[Tim Ferguson]

Any real-world examples?

As usual, *=PK, +=FK...

Students (*StudentID, FName, LName, etc)

Courses (*CourseCode, FullName, NumCredits, etc)

Classes (*CourseCode+, *YearStart, MainLecturer, etc)

Registrations (*StudentID+, *CourseCode, *YearStart, PaidBy
FK (CourseCode, YearStart) references Classes)

Attendances (*StudentID, *CourseCode, *YearStart, *DateOfLecture
FK (StudentID, CourseCode, YearStart) references Registrations)

and so on. You might consult any standard text on R Database design for
more examples.

Hope that helps


Tim F

 

[TC]

Sure, I agree that composite PKs get a bit unweildy once they have more than
a few fields. I can tolerate 2 (definitely), & 3 (if I have to). Beyond that
I would add an auto#, & change the pk fields to a unique index, as you say.

Cheers,
TC