Storing information in linking tables

  • Thread starter Thread starter Tom via AccessMonster.com
  • Start date Start date
T

Tom via AccessMonster.com

Hi
I was told that you shouldn't store information in a linking table (many to
many) except keys, Is this true?

tom
 
You certainly should not be storing the actual data such as an Employees last
name in the linking table. Rather you should be storing the primary key to
the Employee table. What if Ann Smith gets married to Jim Jones and changes
her last name?

However there is other valid data to store in the linking table. For example
I often store when the link was created by having a Date/Time field in the
table with a default of Now(). I'm sure that there are other valid reasons to
do so.
 
Thanks,

What about storing information like for example; If you have these tables,
tblClasses, tblStudents, and had a linking table of tblStudentsClasses. Could
the linking table store info about things like attendance for a certain class
on a certain day?

Are there any cases where you can store info besides foriegn keys?

Tom
 
Hi Tom,

Yes, there are such cases. But you need to think of the "linking" table
not just as a link between two entities (e.g. Students and Classes) but
as modelling a separate entity in its own right.

Here's the first example that comes to mind. You're creating a database
to support travel bookings for a company's employees. So there's a table
of Employees, and a table of Airlines.

But the CE wants to be booked on BA whenever possible so as to maintain
his platinum Executive Club card, the Finance Director prefers American
and refuses to fly XxxxAir under any circumstances, and so on. So maybe
you need an entity EmployeeAirlinePreference, modelled in a table with
fields like this (* means primary key, + means foreign key)
EmployeeID*+
AirlineID*+
FrequentFlierNumber
Preference+ (preferences might be "Never", "Emergency Only",
... "Whenever Possible")
Comment
 
Hi John,
I understand what your saying. It seems that when using a linking (junction)
table in a many to many relationship there will be times when it is correct
to store data in the linking table besides the keys. So much depends on
having a very clear understanding of what the subject of the table is, and
sometime just using a good table name helps in making that clear. I just have
a hard time when the subject of the table has to do with an event and not a
physical thing. For example; several hotels, several conferences, one hotel
can have several conferences, one conference can be held at several different
hotels at different times. I guess I could have a junction table tblEvents
that would contain dates, number of attendants, and also create a many to
many relationship between the hotel and conference tables.

I think I am starting to understand it, this discussion helps.

Thanks,
Tom
 
For example; several hotels, several conferences, one hotel
can have several conferences, one conference can be held at several different
hotels at different times. I guess I could have a junction table tblEvents
that would contain dates, number of attendants, and also create a many to
many relationship between the hotel and conference tables.
Click to expand...

That's the idea. Good database design can involve some very hard
thinking at this stage to work out just what entities and relationships
are needed. Do you need to track individual people (as invitees,
delegates, no-shows, speakers, moderators, panellists or whatever)? If
so, maybe there's one entity, Person, and another, ConferenceInvitation
which would implement a M:M relationship between Persons and
Conferences: whether and when a person was sent an invitation, a
follow-up, whether they responded, paid, attended, and so on.
 
Back
Top