Storing information in linking tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom - 29 Jun 2006 15:15 GMT

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

tom

Reply to this Message

Jerry Whittle - 29 Jun 2006 15:54 GMT

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.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

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

Reply to this Message

Tom - 30 Jun 2006 01:29 GMT

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

Reply to this Message

John Nurick - 30 Jun 2006 06:54 GMT

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

>Thanks,
>
[quoted text clipped - 6 lines]
>
>Tom

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Tom - 30 Jun 2006 15:46 GMT

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

Reply to this Message

John Nurick - 30 Jun 2006 22:22 GMT

>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.

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.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message