Table Relationship

[goestejs]

I work in a manufacturing facility and I am creating a database to track Production Downtime. We have several assembly lines working 3 shifts each day. I have the following table to record the Daily Production where the date, line and shift are the primary key in order to avoid duplication of data:

tblProduction:
ProductionDate (PKey)
LineID (PKey)
ShiftID (PKey)
ShiftLength
QtyProduced
QtyScrapped
QtyReworked

I have another table to record the Daily Downtime which could be for a number of different reasons:

tblDowntime:
DowntimeID (PKey-Autonum)
DowntimeReasonID
DowntimeMinutes

The problem is I am not sure what foreign key I should use in tblDowntime in order to create the relationship with tblProduction. Any ideas?

 

[dhookom]

I would add an autonumber to tblProduction. Use this as the foreign key in tblDowntime. You can still maintain a unique index on Date, Line, and Shift.

Apparently you only produce a single product on the line during a shift?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

You could do what you are doing, using all three keys together to make what is called a composite key.

However to link to this table you would have to hold all three foreign keys
tblDowntime:
DowntimeID (PKey-Autonum)
DowntimeReasonID
DowntimeMinute
productionDateFK
productionLineFK
productionShiftFK

This is acceptable, and some people do this. I do not, it get gets tedious. So what Duane was saying is add an autonumber field and make that the PK. But what was not clear, was he also wanted you to delete the PKs from the original fields.

So like Duane said, create a composite index to ensure that no duplicate data is entered. So you index productionDate, lineID, shiftID and set it to not allow duplicates. That way no two records can have the same combination of date, shift, and line. If you do not know how to make an index look in the help. It is pretty easy.

 

[goestejs]

Thanks dhookom for the advice and thank you majp for the clarification. Creating the composite index and creating an autonum Pkey worked like a charm!