I am trying to make a database that would allow an agency to track job movements across a geographic region. I have laid out the relation in the entity relationship diagram attached. I am not sure if it's normalised or not. The fields that I think need explaining are as follows:
ABN: Business ID
ANZSIC code: classification code for industries (six digit code)
ANZSCO code: classification code for occupations (four digit code)
Job title: An alternative job title whenever the job cannot be covered by an ANZSCO code
Postcode: Zip code
Employment type: Whether full time, part time or casual
Current employees: The current number of employees at the specified date, in the particular job, for the specific business
Terminations: The number of terminations at the specified date, in the particular job, for the specific business
AdvertisedVacancy?: A boolean data type (Yes/no) to cover both advertised and unadvertised vacancies
Vacancies: The number of vacancies (either advertised or unadvertised; part time, full time or casual) at the specfied date, in the particular job, for the specific business.
I wanted to distinguish between current employees and new hires, but I found it too difficult. I saw that there would be maintenance problems with transitioning the data of the new hires to the current employees field. I believe this is because I did not allow for the database to hold information about the employee so that you can track each employee's movement through the business. If someone can help me with this, or if there are any other problems you see, I would really appreciate it.