Table stucture questions

[K3vin]

Hi,

I am building a planning database that will hold staff tables, holidays, sickness, requests so that worked days can be planned.

Just wanting to know if this structures is going in the right direction and should holidays and absence be in the same table with other table with ID of these types?

tblStaffing ID		tblHolidays		PlanningTeamID		tblShifts	tblRequestDetails
Forename		HolidayID		PlanningID		ShiftPatternID	RequestID
Surname		StaffID				PlanningID	PlanningID
EmploymentCategory		PlanningID				StaffID	StaffID
Hub		LeaveType				StartShift	StartDate
Department		StartDate				EndShift	EndDate
FTE		EndDate					StartTime
SupervisorID		Approved/Declined					EndTime
CostCentre							Activity
LatestStartDate							Comments
MondayHours
TuesdayHours
WednesdayHours
ThursdayHours
FridayHours

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>

My other question is format of the shift pattern

StaffID	Monday	Tuesday	Wednesday	-
5	7	7	7	-
9	7	0	7	-

<tbody>
</tbody>

or should it be

StaffID	Date	Day	Hoursworked
5	23/06/2017	Friday	7
9	16/06/2017	Monday	7

<tbody>
</tbody>

 

[Joe4]

Welcome to the Board!

Thinking from a Data Normalization perspective, I would do the following:

1. Your Staffing table should have a unique staff ID field. Names are never good to use, as two people can have the same name.

2. The five "hours" fields should probably be moved out of your Staffing table and into its own table, i.e.

tblStaffingHours
StaffID
DayOfWeek
StartTime
EndTime

3. Regarding your shift pattern question, go with the later option you displayed.

 

[K3vin]

Thanks I have went with your advice got most of the tables built.

The table relation between the tbemployees and the tblTimeCard

am struggling a bit with linking the following 3 tables.

One table has the employees, one with the shift pattern days worked and length of shift then there is the actually time card with what they actually worked each day.

There will be more than one staffID in each of these tables for the timecard and shift pattern so how to I create a unique reference or primary key in these tables.

should I join the autonumber with the staffID to create a primary key or is this going down the wrong path.

tbEmployess

StaffID

SupervisorID

Last Name

First Name tblStaffHours StaffID Workday Hours tblTimeCardID ShiftID StaffID StartShift EndShift <colgroup><col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <tbody> </tbody>

<colgroup><col width="172" style="width: 129pt; mso-width-source: userset; mso-width-alt: 6290;"> <tbody> </tbody>

 

[Joe4]

The relationship between the StaffID and the other two tables will probably be one-to-many, which is OK.
You should have Primary Keys in your other tables. There are a few ways of going about this:
- Using a "Joint" Primary key field (i.e. "StaffID" and "Workday" together make a unique record)
- Create another ID field
- Add an Autonumber field to the table

There really is no one correct way of doing it; people have different preferences.
To see various discussions regarding the benefits of doing it different ways, read these:
https://www.access-programmers.co.uk/forums/showthread.php?t=182610
https://www.access-programmers.co.uk/forums/showthread.php?t=89864

 

[K3vin]

Am still not getting this join on the table between the staffhours and timecard tables

If I use autonumber only how do I bring back the Staffhours ID from the table

This is sample of the data

StaffHoursID	StaffID	OwnerID	WorkDay	Hours
1	SL600	SL200	Monday	7
2	SL600	SL200	Tuesday	7
3	SL600	SL200	Wednesday	7
4	SL600	SL200	Thursday	7
5	SL600	SL200	Friday	7
6	SL1000	SL200	Monday	7
7	SL1000	SL200	Tuesday	7
8	SL1000	SL200	Wednesday	7
9	SL1000	SL200	Thursday	7
10	SL1000	SL200	Friday	7
TimeCardID	StaffHoursID	StaffID	ShiftStart	ShiftEnd
1		SL600	26/06/2017 08:00	26/06/2017 16:00
2		SL600	27/06/2017 08:00	27/06/2017 16:00
3		SL600	28/06/2017 08:00	28/06/2017 16:00
4		SL600	29/06/2017 08:00	29/06/2017 16:00
5		SL600	30/06/2017 08:00	30/06/2017 16:00
6		SL1000	26/06/2017 08:00	26/06/2017 16:00
7		SL1000	27/06/2017 08:00	27/06/2017 16:00
8		SL1000	28/06/2017 08:00	28/06/2017 16:00
9		SL1000	29/06/2017 08:00	29/06/2017 16:00
10		SL1000	30/06/2017 08:00	30/06/2017 16:00

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

 

[xenou]

What do you want to bring back from these tables? You've also got me a little lost because in one post you talk about employees, and in another staff ... which is it? Also now a lot of table names have been thrown out so not sure what tables you are actually using now.