Fancy a challenge? need help with table relations

Get your aspirin and other headache pills at the ready...

I have already posted this in the thread "one-to-many relationships" in
"general questions" but have decided to post here.

After thinking long and hard this is basically the relationship structure
for my employee information database. Staff are either temporary or permanent
and I have tried to summarize the relationships thus:

Permanent staff have:

A) a "one-to-one" relationship with their personal information
B) a "one-to-one" relationship in that they only work in one department of
the office.
C) a "one-to-many" relationship (the "many" being weekly records)

temp staff have one "one-to-one" relationship with their personal information

and two "one-to-many" relationships

A) the first is weekly total hrs and overtime.
B) the second is the number of departments they can work in.

Due to work demands, a temp staff member may not necessarily stay in one
department in the office at any one time (across the 8 departments there are
a total of 25 sub-departments) so I need to record the total hours each temp
employee worked each week in each department.

I really do like a challenge? Anyone else up for it? ;-)

Due to work demands, a temp staff member may not necessarily stay in one
department in the office at any one time (across the 8 departments there are
a total of 25 sub-departments) so I need to record the total hours each temp
employee worked each week in each department.

You have temporary staff members that may work in any of eight departments,
and you need to keep track of the hours that they work in those departments.

tbl_Temps
TempID (PK)
TempInfo

tbl_Departments
DepartmentID (PK)
DepartmentInfo

tbl_TimeWorked
TimeWorkID (PK)
TempID
DepartmentID
WorkedDate
WorkedTime

Draw the relationships in the relationship window. Create a query for the
time period you want to look at (this would be the work week), with all
pertinent fields included. Then create a form or report that uses that
query, and format it appropriately. You could make several forms/reports
that sort by either temporary staff member, or by department.

If you included a field to indicate which week of the year the worked date
falls in, you could even do a yearly report, with all of the weeks sorted out.

B) a "one-to-one" relationship in that they only work in one department of
the office.

This relationship should be many-to-one as one department will have more
than one employee working on it.

Mauricio Silva

"scubadiver" wrote:

Get your aspirin and other headache pills at the ready...

I have already posted this in the thread "one-to-many relationships" in
"general questions" but have decided to post here.

After thinking long and hard this is basically the relationship structure
for my employee information database. Staff are either temporary or permanent
and I have tried to summarize the relationships thus:

Permanent staff have:

A) a "one-to-one" relationship with their personal information
B) a "one-to-one" relationship in that they only work in one department of
the office.
C) a "one-to-many" relationship (the "many" being weekly records)

temp staff have one "one-to-one" relationship with their personal information

and two "one-to-many" relationships

A) the first is weekly total hrs and overtime.
B) the second is the number of departments they can work in.

Due to work demands, a temp staff member may not necessarily stay in one
department in the office at any one time (across the 8 departments there are
a total of 25 sub-departments) so I need to record the total hours each temp
employee worked each week in each department.

I really do like a challenge? Anyone else up for it? ;-)

=?Utf-8?B?c2N1YmFkaXZlcg==?=
wrote in :


After thinking long and hard this is basically the relationship
structure for my employee information database. Staff are either
temporary or permanent and I have tried to summarize the relationships
thus:



Not very well, though: it feels very much as if you have not yet identified
your entities. From what you have posted, this would seem to be a first-cut
minimum set:

People

Departments

Allocation (of people to departments)

WorkingShifts (times people start and finish their shifts)


Relationships:

People -- Allocations -- Departments
|
+- WorkingShifts

If the difference between full time and part time working is critical, then
you could modify this to a sub-typing model but that would be up to you.

Hope it helps


Tim F

Hi,

thanks for pointing that out, but does it makes it more complicated than
necessary?

"Mauricio Silva" wrote:


B) a "one-to-one" relationship in that they only work in one department of
the office.

This relationship should be many-to-one as one department will have more
than one employee working on it.

Mauricio Silva

"scubadiver" wrote:

Get your aspirin and other headache pills at the ready...

I have already posted this in the thread "one-to-many relationships" in
"general questions" but have decided to post here.

After thinking long and hard this is basically the relationship structure
for my employee information database. Staff are either temporary or permanent
and I have tried to summarize the relationships thus:

Permanent staff have:

A) a "one-to-one" relationship with their personal information
B) a "one-to-one" relationship in that they only work in one department of
the office.
C) a "one-to-many" relationship (the "many" being weekly records)

temp staff have one "one-to-one" relationship with their personal information

and two "one-to-many" relationships

A) the first is weekly total hrs and overtime.
B) the second is the number of departments they can work in.

Due to work demands, a temp staff member may not necessarily stay in one
department in the office at any one time (across the 8 departments there are
a total of 25 sub-departments) so I need to record the total hours each temp
employee worked each week in each department.

I really do like a challenge? Anyone else up for it? ;-)

Im not interested in start and finish times, only total hours.

"Tim Ferguson" wrote:

=?Utf-8?B?c2N1YmFkaXZlcg==?=
wrote in :


After thinking long and hard this is basically the relationship
structure for my employee information database. Staff are either
temporary or permanent and I have tried to summarize the relationships
thus:



Not very well, though: it feels very much as if you have not yet identified
your entities. From what you have posted, this would seem to be a first-cut
minimum set:

People

Departments

Allocation (of people to departments)

WorkingShifts (times people start and finish their shifts)


Relationships:

People -- Allocations -- Departments
|
+- WorkingShifts

If the difference between full time and part time working is critical, then
you could modify this to a sub-typing model but that would be up to you.

Hope it helps


Tim F

What I explained in my original message was quite expansive, but I have
simplified it here a great deal and isn't too different to what you propose.

People:

Name
Are they a current worker?
Status (Temp or Permanent)
Hourly rate (or permanent equivalent)
holiday taken (if permanent)

Department:

Cost-centre
Department
Subdepartment

Total hours:

Standard hours
Time and a half
Double time

I also want to somehow include the date (for week-ending). But I am not sure
whether to keep it separate from the other tables.

Mauricio quite rightly says there is more than one person in a department.

I am still trying to figure out the best way to organise the tables.

"Tim Ferguson" wrote:


People

Departments

Allocation (of people to departments)

WorkingShifts (times people start and finish their shifts)

I think you might be right.

I have listed the tables and entities in my 2nd response to Tim's message.

"Mauricio Silva" wrote:


B) a "one-to-one" relationship in that they only work in one department of
the office.

This relationship should be many-to-one as one department will have more
than one employee working on it.

=?Utf-8?B?c2N1YmFkaXZlcg==?= wrote
in :

I am still trying to figure out the best way to organise the tables.


I do not see how it's different from what I posted?


Tim F

Total hours:

Standard hours
Time and a half
Double time

Scuba, these are not field names, they are information about the hours. If
you need these descriptors, they should go into a table of hour descriptors,
and then be referenced in the table where you have a field for total time
worked. They should not be used as field names at all.