Table Relationships

[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?

[weekend00]

I did find any relation between your two tables where you can create relationship.

if you want relationship, you need to add fields ProductionDate ,LineID (PKey),ShiftID to the second table.

The point is, first you need to decide what you goal is, then decide how to design the tables. Not in the way that you create two tables first then think about what can you do with the two tables.

[ajetrumpet]

goe,

weekend is correct in that you have nowhere to issue a relationship. I would suggest thinking about what you want to report. EXACTLY. for example, if your supervisor wants the amount of downtime and at what times they occurred, you need no relationship (and in fact, only ONE table).

your one piece of data that is of concern to you is the DOWNTIME. since that is the case, you really don't need any relationships. you would however need some, if for example your supervisor is concerned with other stuff, like employees during a certain shift, multiple factories, etc, etc...

relationships are used to connect GROUPS of data.


e.g. - employees, factories, downtimes, lines in ONE factory, products produced.

[goestejs]

Thanks for your responses. My issue was solved by creating a composite index for ProductionDate, LineID & ShiftID and then adding an AutoNum field as my Primary Key in tblProduction and using it as the Foreign Key in tblDowntime.