Two foreign Keys in One table.

[Kelemit]

I'm not new to access, and I understand normalization at pretty much all its levels, but right now I'm curious about a situation that I have just come across. The first time I've been in something like this, so I thought I would ask.

This is the setup.

I have employees. They are apart of a shift and a budget. Shifts and budgets are completely independent of each other.

The database needs to keep track of shifts and budgets over time. Therefore, 1 to many relationship to shift table and budget table.

But, the database also needs to keep track of attendance. And the user wants attendance tracked by Shift and Budget.

Shift and budget are completely independent of each other.

Currently, I have the relationships set up like this.

Employee (1) ---(Many) Budget (1)------(Many) Attendance
Employee (1) ---(Many) Shift (1)--------(Many) Attendance

When a new attendance record needs to be added, both the key to the budget and shift are added to the attendance table. The key chosen is dependant onthe Employee chosen, and whether the budget and shift are the CURRENT budget and shift that the employee is apart of.

I thought of running a query showing budget and shift by date descending, so that the latest budget / shift would be displayed, and thus the most current (SUPPOSEDLY) But, if a user put in a different date, or screwed up on the date, then the incorrect shift and/or budget would be displayed.

Date stamping an entry was an option, but there needed to also be a user entered date as well, to specify WHEN a user began working in that specific budget / shift. Therefore two date entries would be required. Duplicate entries in most cases.

I therefore decided to go with a true/false yes/no checkbox. Where the current budget or shift would be checked, and all non current ones would be unchecked (false).

Currently, this is how the systems works. And it works well. But it is dependent on some form code I created to set the yes/no checkbox to true/false depending on the situation.

I DON"T like doing this. Am I missing a way to do this "correctly" where by Access would do this "automagically" instead of via my trick.

The ONLY issue really, is that when a new attendance incident occurs, the user needs to put in the incident to the approbriate shift / budget. And if the current shift / budget could automatically be displayed without user intervention (IE user has to pick the shift / budget from drop down box after looking up info etc etc) since the current information SHOULD be known.

I've never done a table setup where two foreign keys are the many side of the relationship in a single table.

It is currently working fine, and seems to do well, but I wanted to make sure with others who might have had this experience. And also, any "advice" / "cautions" for this kind of situation so I don't step into it deep and have to fix it later.

 

[Len Boorman]

It is not that uncommon to have more than one foreign key in a table. It is in fact very common when you need to decompose Many to Many relationships. These decompose into two 1 to many relationships and therefore immediately you have 2 foreign keys in a table
Len

 

[Kelemit]

Well, thank you len...

But that doesn't help me much now does it... lol.

The point is not whether there can be 1 foreign keys, the point is that two foreign keys with the idea that there is a "time" flow. IE only one valid set of shift and budget information at any ONE time.

There are many PAST shifts and budgets for any one employee, but there is only ONE CURRENT budget and ONE CURRENT shift for an employee at any given time.

How to (the best way) find the CURRENT budget and CURRENT SHIFT for each employee.

Is the way I'm doing it good? or is there another / better way.

Kelemit

 

[Len Boorman]

Okay so you can have two foreign keys.

Now there is not a problem having another field that determines so Date/Time information.

So you can have many Budget/Shift combinations with a date qualifier.

Say date qualifier is Year.

You can have past Budget/Shift combinations because the year qualifier has a value of 2005, 2004 etc.

So you have not only current (Max of Year) but also a meaningful historical record

Len

 

[Kelemit]

That is understood Len.

But at this time, the Date is inputted by a user. IE they have to type in the month / day / year.

So max of such (or descending order, chose record one) will show the latest info for the budget or shift.

Except, that if you want to put in attendance information for an employee via forms. You gotta make an employee form, then a budget / shift subform (this is the tough part, because these are two completely independent pieces of information) then create the attendance subform so that the shiftid / budgetid are added to the attendance form.

IF you do a query for max (shift.date) and max(budget.date) and the dates for both shift and budget ARE DIFFERENT, then you get some crazy meaningless information, even with the "max" function, let alone without.

Creating a recordsource for this subform for the budget / shift will return meaningless records. It will create a record for each combination of a budget and shift. If there are two budgets and two shifts. It will return 4 records: budget one with shift one, budget one with shift two, budget two with shift one and budget two with shift two.

All of those combos are meaningless, except ONE. the one which shows the two CURRENT records. Throw in 3 records of each, and now you have 9 records, 8 meaningless, 1 needed. And on top of that, its possible that 4 of the 9 are IMPOSSIBLE combinations to actually create an attendance record, and while the other 5 are, only 3 of the combos were actually used.

Does this all make sense now?

Kelemit

 

[Len Boorman]

Must admit I am still a little lost on what you are trying to do but a few comments.
1) When you create a shift/budget records you have a single date (maybe date the combination was created) Then when you get the Max of date you get a single pair of values.

2) You could have an autonumber as a PK in this table (have a unique index on the Shift/budget combination to prevent duplicates.

3)You could then use the a combo with the autonumber as the bound column when creating an attendance record. That way you can associate a specific budget/shift record to an attendance record.

L

 

[Kelemit]

Must admit I am still a little lost on what you are trying to do but a few comments.
1) When you create a shift/budget records you have a single date (maybe date the combination was created) Then when you get the Max of date you get a single pair of values.

But then I have to create ONE table for shift AND budget, and that makes shift and budget NOT 3nf. Also, the shift and budget are then not independent, and anytime a shift or budget is changed, both shift and budget must be reassigned. Because the shift might or might not change when a budget does, and vice versa.

If you keep the shift and budget on separate tables (to maintain NF), then the shift and budget CAN HAVE different dates as to when the employee joined that specific shift or budget.

To create a table to make a single date as to when those two "melded" or whatever, ends up being the same problem, Two Foreign keys in one table. Two tables IN COMBINATION being the link to another SINGLE table.

2) You could have an autonumber as a PK in this table (have a unique index on the Shift/budget combination to prevent duplicates.

To have an autonumber PK that uniquely indentifies the shift budget combo ends up being the exact same thing as just linking straight to the attendance table. What you are talking about is jsut adding another table intot he relationship. That table will have the same problem.

Instead of
Employee ----Shift-------Attendance
...............\--Budget--/

you have

Employee------Shift--------ShiftBudgetUniquePKTableThing-----Attendance
.................\--Budget---/

This just moves the problem to another table, as I can see it.


3)You could then use the a combo with the autonumber as the bound column when creating an attendance record. That way you can associate a specific budget/shift record to an attendance record.


Still creates the above problem. And the user, if I understand correctly, still has to chose teh appropriate shift / budget eh user is in.

IF I WANTED the user to choose the shift / budget, it wouldn't be a problem.

The problem is that the CURRENT shift and budget is KNOWN, AND its the only one that matters.

Past shifts and budgets are kept ONLY for reporting purposes. Past attendance issues will be (possibly or possibly not depending on whether the employee HAD an attendance issue during the time he was working that shift / budget) stored LINKED to the shift and budget.

Any NEW attendance incidents SHOULD be automatically applied to the employees current shift / budget. The current shift and budget is known.. why should I have to make the user pick the shift and budget...

But HOW do I display / show ONLY the current shift and budget in such a way that the 2 FOREIGN keys are automagically applied to the attendance sheet.

ShiftID (Made by the shift table)
BudgetID (Made by the Budget table)


Need to create a form with both those ID's on it, such that a child form containing a link to attendance will have shift and budget automatically linked. But to create a form with shift and budget as a datasource, I need to make a query combining the two... .but combining the two returns multiple meaningless records.

This appears to be an example of BCNF. In fact, i'm 99% certain it is, but even creating a BCNF table still begs the question of how we return the latest (IE current) shift and budget... man..

I gotta figure this out.. i'm getting really bugged by this now

Kelemit

 

[Len Boorman]

You said originally that "I have employees. They are a part of a shift and a budget".
So the employees budget and shift are already defined, The attendance record for an employee is simply that. Employee and attendance.

Attendance table has Employee and Attendance
Employee Budget Shift has exactly that Employee Budget and Shift.

Additionak tables for Budget details, Shift Details and Employee details

Perhaps

L

 

[The_Doc_Man]

This smells highly of a missing table, one called "attendance." (Or something like that.)

Your problem is clear to me. You are asking a question for which your answer is convoluted because your schema doesn't directly support the query.

There are many PAST shifts and budgets for any one employee, but there is only ONE CURRENT budget and ONE CURRENT shift for an employee at any given time.

Since you have made it clear that budget and shift are independent, you need independent queries to determine these independent values. The bit about checking a yes/no box is the wrong answer, usually. However, there is always the possibility that you could have the current employee foreign keys for budget and shift in the employee table as the result of an automatic update query as described above. Once you have those keys, I don't see a problem. So my answer to your question about the best way to do something is that whenever you have a change of assignment (budget or shift), you automagically run two update queries that find the max date for the budget assignment actions and the max date for the shift assignment actions. Since they are independent values, set theory says you must ask the questions independently (if I remember my set theory).

To create a table to make a single date as to when those two "melded" or whatever, ends up being the same problem, Two Foreign keys in one table. Two tables IN COMBINATION being the link to another SINGLE table.

You have attempted to merge two items in an apples-and-oranges method. If they are independent, the dates for each reassignment are independent. To make one date fit two events is not possible unless both events came about at the same time. Low odds on that, I'll wager.

Let me put it another way. The foreign keys in the employee record have every right to be there. They might need updating now and again, but they can be there. But the date associated with the reassignment has NO right to be in the employee table if you are making the "one date fits all" treatment. And I keep on seeing the "one date" concept in this thread. This one date depends on two independent events, which means something is NOT 3NF despite your claim that you have cleaned up your DB.

I guess I've belabored the point, but maybe it will help you see your problem better. You are right to keep your budget assignment events and your shift assignment events in different tables. But when you try to carry too much over from the independent tables, you run into the conceptual problem you have described.

Len's answer is right. I see several tables.

1. Employee record. Has two FK's to budget and shift tables.
2. Budget (master) record. Semi-static list of allowed budgetary codes. Probably changes as your contract/job mix changes.
3. Shift (master) record. Probably very static list of allowed shift codes. Changes only when your goverment or business model changes your hours of operation.
4. Budget assignment record. Has date, FK's to Employee, Master Budget. Maybe nothing else. Maybe FK to authorized employee who made the change to the record. Maybe not.
5. Shift assignment record. Has date, FK's to Employee, Master Shift. Same concept as budget assignment record.
6. Attendance record. Has date, FK to employee, shift, budget - BUT it gets shift and budget entries from separate queries (DMax date for employee from budget assignment, shift assignment tables). In essence, you get second-hand info on correct budget number from assignment records.

If you are worried about charging hours, they go in the attendance record or another table. Play with this in your head a bit, see if it starts to make sense with my table descriptions.

 

[WindSailor]

I know you said you were up on all levels of Normalization, but here is a link for a refresher course if interested, there are examples of many to many etc. in some of the links...

http://www.access-programmers.co.uk/forums/showthread.php?t=100211

 

[Kelemit]

This smells highly of a missing table, one called "attendance." (Or something like that.)

I agree, there might be a missing table, but i honestly cannot see it. All data is in its approbriate place, as best as I can see it, each linked / connected to ONLY the primary key, period.

Len's answer is right. I see several tables.

1. Employee record. Has two FK's to budget and shift tables.
2. Budget (master) record. Semi-static list of allowed budgetary codes. Probably changes as your contract/job mix changes.
3. Shift (master) record. Probably very static list of allowed shift codes. Changes only when your goverment or business model changes your hours of operation.
4. Budget assignment record. Has date, FK's to Employee, Master Budget. Maybe nothing else. Maybe FK to authorized employee who made the change to the record. Maybe not.
5. Shift assignment record. Has date, FK's to Employee, Master Shift. Same concept as budget assignment record.
6. Attendance record. Has date, FK to employee, shift, budget - BUT it gets shift and budget entries from separate queries (DMax date for employee from budget assignment, shift assignment tables). In essence, you get second-hand info on correct budget number from assignment records.

This is exactly what I have, as I stated earlier / before.

The ONLY exception to this list that I currently have is that the attendance record is linked THROUGH the shift and budget tables, since the attendance is DEPENDENT ON the shift AND budget. THAT is my point.

I guess I could basically link the attendance directly to the employees table, and then reduplicate all the data in the attendance table, but that is exactly what NF IS NOT.

I honestly appreciate your responses guys. This is helping me alot. Most NF issues are very easy / quick for me to settle. This one has me scratching my head a bit.

I guess I am not explaining my self as correctly or well as I could.

Let me try again.

I HONESTLY believe this is actually a BCNF issue. But am not exactly certain.

Explanation: Part Tres.

Employee is base table, no foreign keys, its PK is the foreign key for just about all other tables that have consistently changing data.

Shift Budget and several other tables have the EmployeeID as a foreign key, and Shift BUdget also have their own PK.

The Shift and Budget IDs are FKs in Attendance.

Options exist, as stated, to do max of date for shift, and max of date for budget. Again though, to get the link necessary, you need returns from BOTH budget and shift as a single "entity," as it were, to return teh appropriate attendance records linked to that shift budget and ultimately the employee THROUGH the shift and budget.

Creating a query so that returns of shift and budget all at once returns duplicate data as well, PRECISELY BECAUSE shifts and budgets don't necessarily, and many times aren't assigned on the same date.

Your option, Doc Man, is interesting, but in essence, would be replicating what is in the Shift and budget tables 2/3's of the shift and budget tables.

There are only 4 things in each. the Employee FK, the PK, shift (or budget) linked as stated to static tables listing available shift / budgets, and the date the shift / budget was assigned to that individual.

To do as you suggest, Doc man, would mean to duplicate both the shift and budget info INTO the attendance table.

In reality, the only information needed in the attendance table ( the ONLY info dependent SOLELY on the PK of the attendance table) is the date of the incident, and what type of incident it was. (IE: Late, absent, sick, etc).

To do as you suggest Doc Man woudl also add 2 new fields: shift and budget. These are not in anyway related to or dependent upon attendance. They are separate from attendance and only INFORM it. Shift is independent of everything except itself, as well as budget, which is why I put them in their own tables.

As I understand it. Doc Man's Idea for the relationships would be something like

Employee -- Budget--\
.............|--Shift-----|
.............|................|
..............\_Attendance

with either the PK of shift and budget, or the actually shift / budget info being duplicated into Attendance via a query / combo box / etc returning max of date in both shift and budget.

Why employee would need a FK to attendance as well as Budget and shift is beyond me. It shouldn't, if Budget and Shift already have FKs into attendance.

Except for the FK from Employee, this is how I currenlty have my relationship set up. A chain going from Employee to BUdget/Shift into Attendance.

But it is the combination of Keys (Budget and shift) that uniquely identify attendance records to budget and shift in combination.

Hmm.. i don't know that I can really explain this well.

Below is a view of my relationship window.

 

[Len Boorman]

1) Realations in BCNF are not necessarily in 3NF. 3NF considers transient dependancies. BCNF does not.
2) Looking at the diagram I suggest that the Attendance table an demployee table should be directly linked and not via trhe Shift/Budget tables.

Quote In reality, the only information needed in the attendance table ( the ONLY info dependent SOLELY on the PK of the attendance table) is the date of the incident, and what type of incident it was. (IE: Late, absent, sick, etc). Unquote


Surely one of the attributes of attendance is who the heck it relates to. The attendance does not relate directly to Shift/Budget.

The employee table basically needs to go between Attandance and the Budget/Shift tables

Len

 

[Kelemit]

CRAP!
I think you might have it Len.

I think it would work for individual incidents, like below:

how to display an incident by a shift or budget, especially when the dates are different.

Attendance would return date 2/5/06 for an incident.

Budget and Shift both, respectively, have dates of 3/8/06 and 4/9/06. These are the current shift and budget.

Obviously, these AREN"T the shift and budget of that incident. I would have to get the LAST shift and incident JUST before this date.
Hmm.

Query for Shift or budget.

SELECT {TOP 1} table.*
FROM table
WHERE ((([Date])<[Date of Attendance Incident]))
ORDER BY table.[Date] DESC;

I don't really need the {top 1}

I'm trying to decide if this would work with this situation. It would independently return the budget and the shift up to the last one JUST before the date of the attendance incident. Probably should make it "<=" and not just "<" because if an incident occured on the same day as a shift or budget change, that incident would be applied to that shift or budget applied that day.


I think that would return the approbriate shift / attendance for an incident.

But .. CRAP CRAP CRAP!!

My one problem as I'm going over in my head.

Returning incidents by shift and/or budget...

But then as I think about it, you just do the exact same thing above, submit a query to run for each incident returning the last record (or first in Desc) just before the date of the incident.

I apologize, but my expertise is not in queries. I've gotten OK at them... but my skill is not the strongest in that area... so I'm just kinda running through my head right now trying to put it together.

I think it would work... give me a bit.

Kelemit

 

[Len Boorman]

Listen

1) Have you amended the relationships.
2) In the attendance youi will have a shift and budget ref along with Person and the date incident ocirred.

This means that the incident has a specific Budget reference against it
This also means that the incidence will have a specific budget reference against it.

The date the shift ref or budget ref were raised is surely irrelevant. They were the applicable references at the date the incident was recorded.

Your problem is how to ensure that correct budget and shift references are entered against the incident. Hopefully you will do this via combo boxes that ensure correct selection.

Then the reporting becomes simple.

The art it to get the data in correctly. Then it comes out correctly with no fuss or bother

len

 

[Kelemit]

But then I'll basically bet duplicating all the data in shift and budget. That was my whole point from the beginning.

Why add 2 new fields to attendance for the shift and budget table, thus doubling the size of the attendance table with ALL PERTINENT data relating to this information is already stored IN ANOTHER TABLE.

you DUPLICATE data.

I'm not trying to be a jerk here, I"m really not.

I'm trying my best to understand this. But they way you are describing this does NOT make sense.

Basically you are recommending:

Shifttable
Shift - Date

Budgettable
Budget - date

Attendance table
Incident - Date - Shift - Budget.

WHY!

We already have the shift and budget info.

Its duplicating unecessarily.

hmm...

 

[Len Boorman]

There are choices and only you can make the call because only you know the full story of what you want to achieve.

Tables are

Person
Attendance
Budget
Shift.

Forget about whats in them for amoment

Now think about relationships.
A person is linked One budget
a Budget is linked to Many People

A person is linked to one shift
A shift is linked to many people

A person makes many attendancies
An Attendance is linked to One person


Have I got them all ?

If so there are no M:M and so no relationships need decomposing.

Matter now of identifying attributes and PK for each table

The PK of a table on the 1 side now gets put into the table on the Many side as a foreign key.

Not trying to be smart or anything like that but that is the process.

Once the relationships are defined then that defines the links and what FK's are required and from where.


Why add 2 new fields to attendance for the shift and budget table, thus doubling the size of the attendance table with ALL PERTINENT data relating to this information is already stored IN ANOTHER TABLE.

Because you need the FK's to define the relationships

I believe that a sit back and quiet review are required. Write out the Rules of you enterprise . They are the be all and end all from which you define relationships.

L

 

[Len Boorman]

Quote
how to display an incident by a shift or budget, especially when the dates are different.

Attendance would return date 2/5/06 for an incident.

Budget and Shift both, respectively, have dates of 3/8/06 and 4/9/06. These are the current shift and budget.
Unquote

The point is that when the incident was recorded the Budget ref in the attendance table (FK) relates to a specific entry in the Budget table. So basically when you query a particular incident/s you get the Budget ref applicable at the time the incident record was created, Whether the Budget ref applies to i month or a whole year is not a problem. Same basis with shift.

Len

 

[Kelemit]

Agreed.

But now the question then gets back to the very first quesion I had in the first place.

How to get the appropriate Shift & Budget IDs ( And now on top of the EmployeeID) to be put into the attendance table showing all 3 approbriate IDs pertinent to that moment.

Creating a query to show all 3 will, approbriately, show the employee, and teh shifts & budgets for that employee. It will also show EVERY possible combination of shift and budget together under that employee.

How do you only return 1 record with the right info. Employee, current shift, and current budget, so that the attendance information is inputted approbriately with the right budget, shift and employee IDs.

Even with max (budget.date) and max(shift.date) will at minimum return 2 records (if I understand correctly) because one record is returning the max budget date, and the other is returning the shift budget date, correct? And even then, it would return (I THINK!!, don't take my word on this) every combo where the shift.date is maxxed and all budget.dates, and the opposite: every combo of max(budget.date) with every shift record for that employee.

Or is my assumption wrong in this situation.

I say this because this has been what has been stumping me right now in the first place. How to return the correct shift and budget information for teh purposes of entering an attendance incident and have the shift & budget IDs inputted into the table.

Thanks alot for your help. Really.

I probably am looking very dense at the moment. Feeling that way as well. Sigh.

Kelemit

 

[Kelemit]

Ok. I see whats going on.. sigh...

I apologize profusely.

I was correct that all records are returned, and with all of them in a meaningless jumble of all possible combinations of records.

Setting the Max value seems to have no affect whatsoever.

But ordering the 2 dates by Descending seems to return the latest dates for budget and shift.

I still don't know that I really like this setup, mainly because if ANYONE accidentally types in the wrong date for the budget or shift change, all future attendance incidents will be applied to the wrong records for shift and budget.

I don't like allowing possible user screw ups like that..

Kelemit

 

[Len Boorman]

Okay we are rolling.

When you create the Form to add a record I suggest that you use Combo boxes.

So here we need to get a little clever.
First in the budget table you have say Budget Ref and a Date. Budget ref being the PK.

The source of the combo box is a query that selects the Budget ref with the Max Date. This will give the user a choice of 1. That avoids them picking the wrong one.

Same sort of stint with teh Shift reference. Give them a choice of 1.

In both cases set the combo box Limit to List as true or yes (cannot remember which)

So when yo query an incident you will get the Budget ref and Shift that relate to the incident and you do not really care about the Budget or Shift reference dates.

Now not sure of your exact situation but if you have employee ID in both Shift and Budget tables you can tie this in to the Combo box sources as well. The first entry a User will select is the employee reference. So you know who the incident relates to.

So you have a complete tie down of what can be entered.

Len