Junction Tables

[ken]

Hi,
Do Junction Tables need ID fields with Primary keys? For example if you
store employee ID, employee name, Company ID, Company name. As you can
see no field here can be a primary key since all of the columns will
have repeatable values. So does one need to put in an autonumber field
and make it a primary key?

thanks

[lesperancer]

assuming
many company IDs, many employee IDs
assigned employee IDs

tblCompany
companyId
companyName

tblEmployee
pkId (autonumber)
employeeId
employeeName
companyId

lets you change the employeeId if required

[ken]

I assume pkid is just a column with no value other then to have it be
an autonumber primary key correct? Anotherwords you recomend having
primary keys in all tables? Will it be practical to do this even though
I may delete some records from these columns in the future?

[Douglas J. Steele]

While you can use an Autonumber field, you really want employee id and
company id BOTH to be at least a unique index, to prevent duplicate entries
from being insert. As well, since Employee Name and Company Name can be
derived when you know the Ids, they shouldn't be stored in the Junction
table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"ken" <ge****@gmail.com> wrote in message
news:11********************@o13g2000cwo.googlegrou ps.com...

Hi,
Do Junction Tables need ID fields with Primary keys? For example if you
store employee ID, employee name, Company ID, Company name. As you can
see no field here can be a primary key since all of the columns will
have repeatable values. So does one need to put in an autonumber field
and make it a primary key?

thanks

[lylefair]

An autonumber primary key is unlikely to be an encumbrance or to create
slowdown for a linking (or junction) table.
It could be useful when we want to classify the relationship link.
That is we might have another table categorizing employer-employee
relationships, perhaps something like, hourly, eligible for vacation
pay, eligible for severance, health benefits, whatever. The primary key
of the employer-employee table could then be used in another table
linking that to the employer-employee relationships table in a many to
one mapping. (A one to many relationship is quite impossible.)
In some cases bound forms and recordsets will not update unless a
unique index exists and can be found and identified by whatever
technology is being used to make the connection or doing the binding. A
Primary Key is an easy way to be sure that this requirement will not
jump up and "gotcha" at some inopportune time.
All my tables have autonumber primary keys, or their equivalent.

I would not make a unique index on employer pk-employee pk, but I might
add a timestamp kind of column. With such a column we would have a
record that John Doe worked for Apple from whenever to a certain date,
and then began to work for Hewlett Packard.

[lesperancer]

yes, pkId is such a column....
I don't always use a 'autonumber' field as a key, it depends on the
data

in most applications, the employeeId field is used as a foreign key in
other tables (vacations, scheduling, payroll), so deleting it out of
tblEmp means deleting its records from all the other tables too

usually, for tracking purposes, business rules would prevent the
deletion of the data, in that case, I'd add 'start date', 'end date'
fields to the table to track when the employeeId is valid

but if you can delete the record and if you use autonumber keys, and
assuming you have keys 1,2,3,4,5,

if you delete 3, and you add a new record, the list is 1,2,4,5,6
then record '3' is gone until you copy the existing table into a new,
duplicate table (tblEmpNew) and rename tblEmpNew to tblEmp
ken wrote:

I assume pkid is just a column with no value other then to have it be
an autonumber primary key correct? Anotherwords you recomend having
primary keys in all tables? Will it be practical to do this even though
I may delete some records from these columns in the future?