Beginner question - recording different employment times for staff members

[drckarcher]

Hi,

I've got a problem which I think might be simple to solve but I just can't figure out how to do it. I might be looking fro the wrong terms (not yet familiar with the database lingo)

I have a table (table_staff) with staff member details such as last name, email and phone number.

I need a way to record  employment start and end dates as well as the job role for those staff members. Some staff would have more than one entry. This could look like..

SmithJ 01.01.2021 to 31.03.2021 JobA
SmithJ 01.04.2021 to 01.06.2021 Job B

MillerT 01.01.2021 to 31.03.2021 JobB

Finally I want to be able to pull the data for a staff member into their staff profile.

I hope this makes sense. Any help is appreciated.

Many thanks Christian

 

[kpcollier]

@drckarcher, will the users have 1 role each or more than 1? I personally have a Role field in my User Table, as each user is assigned to 1 role (that can be changed if needed). I am not sure if your Role is related to the User's hierarchy in the company or related to the specific Job itself. If the former, I would add Role to the Staff table. If the latter, I would add Role to the Job_Schedule_Table (see below).

As for the jobs and assigned dates, I believe you should create more tables. One for all of the Jobs and one for assigning staff to a job. Put all of your job information in the Job Table (Job name, address, contact, etc). Then, in the Job_Schedule_Table (or whatever you want to call it) you would have a field for User ID, Job ID, Start Time, End Time, (add Role here if it is for Job-level, discussed above) and whatever else you would like to include.

This way you can assign as many users to as many jobs as you want and have the database be normalized. You will be able to pull data and create views on all of these tables to create staff profiles.