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?

[TABLE="width: 758"]
<tbody>[TR]
[TD]tblStaffing ID[/TD]
[TD][/TD]
[TD]tblHolidays[/TD]
[TD][/TD]
[TD]PlanningTeamID[/TD]
[TD][/TD]
[TD]tblShifts[/TD]
[TD]tblRequestDetails[/TD]
[/TR]
[TR]
[TD]Forename[/TD]
[TD][/TD]
[TD]HolidayID[/TD]
[TD][/TD]
[TD]PlanningID[/TD]
[TD][/TD]
[TD]ShiftPatternID[/TD]
[TD]RequestID[/TD]
[/TR]
[TR]
[TD]Surname[/TD]
[TD][/TD]
[TD]StaffID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PlanningID[/TD]
[TD]PlanningID[/TD]
[/TR]
[TR]
[TD]EmploymentCategory[/TD]
[TD][/TD]
[TD]PlanningID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]StaffID[/TD]
[TD]StaffID[/TD]
[/TR]
[TR]
[TD]Hub[/TD]
[TD][/TD]
[TD]LeaveType[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]StartShift[/TD]
[TD]StartDate[/TD]
[/TR]
[TR]
[TD]Department[/TD]
[TD][/TD]
[TD]StartDate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EndShift[/TD]
[TD]EndDate[/TD]
[/TR]
[TR]
[TD]FTE[/TD]
[TD][/TD]
[TD]EndDate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]StartTime[/TD]
[/TR]
[TR]
[TD]SupervisorID[/TD]
[TD][/TD]
[TD]Approved/Declined[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EndTime[/TD]
[/TR]
[TR]
[TD]CostCentre[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD]LatestStartDate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]MondayHours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TuesdayHours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WednesdayHours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ThursdayHours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FridayHours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>[/TABLE]






My other question is format of the shift pattern


[TABLE="width: 100"]
<tbody>[TR]
[TD]StaffID[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

or should it be
[TABLE="width: 500"]
<tbody>[TR]
[TD]StaffID[/TD]
[TD]Date[/TD]
[TD]Day[/TD]
[TD]Hoursworked[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]23/06/2017[/TD]
[TD]Friday[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]16/06/2017[/TD]
[TD]Monday[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="width: 129"]
<colgroup><col width="172" style="width: 129pt; mso-width-source: userset; mso-width-alt: 6290;"> <tbody>[TR]
[TD="width: 172, bgcolor: transparent"]tbEmployess[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]StaffID[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SupervisorID[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Last Name[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]First Name

tblStaffHours
StaffID
Workday
Hours

[TABLE="width: 73"]
<colgroup><col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <tbody>[TR]
[TD="width: 97, bgcolor: transparent"]tblTimeCardID
ShiftID[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]StaffID[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]StartShift[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]EndShift[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="width: 851"]
<tbody>[TR]
[TD]StaffHoursID[/TD]
[TD]StaffID[/TD]
[TD]OwnerID[/TD]
[TD]WorkDay[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]SL600[/TD]
[TD]SL200[/TD]
[TD]Monday[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]SL600[/TD]
[TD]SL200[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]SL600[/TD]
[TD]SL200[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]SL600[/TD]
[TD]SL200[/TD]
[TD]Thursday[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]SL600[/TD]
[TD]SL200[/TD]
[TD]Friday[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]SL1000[/TD]
[TD]SL200[/TD]
[TD]Monday[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]SL1000[/TD]
[TD]SL200[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]SL1000[/TD]
[TD]SL200[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]SL1000[/TD]
[TD]SL200[/TD]
[TD]Thursday[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]SL1000[/TD]
[TD]SL200[/TD]
[TD]Friday[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TimeCardID[/TD]
[TD]StaffHoursID[/TD]
[TD]StaffID[/TD]
[TD]ShiftStart[/TD]
[TD]ShiftEnd[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]SL600[/TD]
[TD="align: right"]26/06/2017 08:00[/TD]
[TD="align: right"]26/06/2017 16:00[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]SL600[/TD]
[TD="align: right"]27/06/2017 08:00[/TD]
[TD="align: right"]27/06/2017 16:00[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]SL600[/TD]
[TD="align: right"]28/06/2017 08:00[/TD]
[TD="align: right"]28/06/2017 16:00[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]SL600[/TD]
[TD="align: right"]29/06/2017 08:00[/TD]
[TD="align: right"]29/06/2017 16:00[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]SL600[/TD]
[TD="align: right"]30/06/2017 08:00[/TD]
[TD="align: right"]30/06/2017 16:00[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]SL1000[/TD]
[TD="align: right"]26/06/2017 08:00[/TD]
[TD="align: right"]26/06/2017 16:00[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]SL1000[/TD]
[TD="align: right"]27/06/2017 08:00[/TD]
[TD="align: right"]27/06/2017 16:00[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]SL1000[/TD]
[TD="align: right"]28/06/2017 08:00[/TD]
[TD="align: right"]28/06/2017 16:00[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]SL1000[/TD]
[TD="align: right"]29/06/2017 08:00[/TD]
[TD="align: right"]29/06/2017 16:00[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]SL1000[/TD]
[TD="align: right"]30/06/2017 08:00[/TD]
[TD="align: right"]30/06/2017 16:00[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

 

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