Fancy a challenge? need help with table relations

[scubadiver]

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? ;-)

 

[mnature]

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.

 

[Mauricio Silva]

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

 

[Tim Ferguson]

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

 

[scubadiver]

Hi,

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

 

[scubadiver]

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

 

[scubadiver]

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.

:

 

[scubadiver]

I think you might be right.

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

 

[Tim Ferguson]

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

 

[mnature]

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.

 

[mnature]

Scuba, this is a bit more normalized than what you have been listing:

tbl_Personnel
PersonnelID (PK)
PersonnelName (don't make the name the primary key)
PersonnelStatus (temporary, permanent, inactive)
PersonnelRate
CostCentreID (I'm assuming that a cost centre is dependent on Department and
Subdepartment)

tbl_Departments
DepartmentID (PK)
DepartmentName

tbl_Subdepartments
SubdepartmentID (PK)
SubdepartmentName

tbl_CostCentres
CostCentresID (PK)
DepartmentID (FK)
SubdepartmentID (FK)

tbl_HoursWorked
HoursWorkedID (PK)
PersonnelID
DateWorked (this could be for either daily or weekly entry)
HoursWorked
HoursWorkedTypeID (FK)

HoursWorkedTypes
HoursWorkedTypeID (PK)
HoursWorkedTypeText (such as standard, time-and-a-half, double)

 

[mnature]

OOPS. Typo in tbl_CostCentres. I apologize. Should look like this:

tbl_CostCentres
CostCentreID (PK) <---- I had an extra "s" in here
DepartmentID (FK)
SubdepartmentID (FK)

 

[scubadiver]

Sorry, but I think that is too complicated.

Go to "how do I change a relationship from 0-2..." (about 11-12 up from this
one!)

But my tables have changed a little bit since that post.

 

[scubadiver]

Just trying to be a bit more informative.

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.

 

[scubadiver]

Im not interested in start and finish times !!

 

[Immanuel Sibero]

Scoob,

PMFJI, but accepting or rejecting a design based on whether or not you
*think* it's complicated is a radical idea <G>. Complicated problems
probably require complicated solutions. The proposed design that mnature
offers is a place to start. Whether or not it is correct depends on how well
and how complete you have stated your problems/requirements.

Honestly, reading through this thread, I wouldnt be surprised that the
solution to your requirements is actually even more *complicated* than what
mnature has offered. The right design is not necessarily the one that you
think is simple or complicated, it's the one that works. The one that works
is the one that is properly developed using relational data model.

If you find yourself rejecting a proposed design simply because you think
it's too *complicated*, then I think you need to brush up on relational data
modeling and normalizations a little more.


HTH,
Immanuel Sibero

 

[John Nurick]

If you find yourself rejecting a proposed design simply because you think
it's too *complicated*, then I think you need to brush up on relational data
modeling and normalizations a little more.

Either that, or simplify the business!

 

[scubadiver]

Mnature,

I am saying this *could* be too complicated because in my database:
department, sub-department and cost-centre are in one table. I have even
included the fields for working hours in the same table because it makes
sense to me.

I don't understand why costcentreID should be in with personnel because
staff may work for more than one department.

HoursworkedID? Hoursworkedtype?

To me, that sounds redundant, but then this is the first time I have built a
database this complicated from scratch and it is a headache.

 

[scubadiver]

that would be nice!

Either that, or simplify the business!

 

[scubadiver]

I should have added what my tables look like at the moment (left to right).

Employee

Name (PK: 1)
Status
Rate

Date

Name (Many)
Date (PK: 1)

Link

date (many)
dept (many)

Dept

Dept (PK: 1)
Subdept
Costcentre
Standard
timehalf
double

Can someone tell me what is wrong with this, or how you interpret this?

thanks