Table structure issue

[lushh]

i am currently working with a human resource outsourcing company. we deploy contractual employees to different companies.
we usually use ms excel in keeping records of our employees, now my boss wants me to create a database using ms access. this is my first time in creating database using ms access.

here are the headers that we used with our excel database:

- ID number
- Account number
- First name
- Last name
- Middle initial
- Start date with the agency
- Start date with the company
- Status (active, end contract, resigned, terminated)
- Previous contract start
- Previous contract end
- Present contract start
- Present contract end
- Company
- Outlet location
- Position
- Pay class (daily or monthly)
- Basic Rate
- Allowance
- Total Pay
- Home address
- Home tel. no.
- Mobile no.
- Civil status
- Date of birth
- SSN

what i did was divide the headers into several tables for ms access as follows:

1st table (Personal Info)

- ID number
- First name
- Last name
- Middle initial
- Home address
- Home tel. no.
- Mobile no.
- Civil status
- Date of birth
- SSN

2nd table (Company info)

- Company ID
- Company Name
- Company address
- Contact number
- Contact person

3rd table (Position info)

- Position ID
- Start date with the agency
- Start date with the company
- Status (active, end contract, resigned, terminated)
- Previous contract start
- Previous contract end
- Present contract start
- Present contract end
- Pay class (daily or monthly)
- Basic Rate
- Allowance
- Total Pay

4th table (Outlet Info)

- Outlet ID

5th table (Status info)

- Status ID

6th table (Pay class info)

- Pay class ID

7th table (Pay class info)

- Civil status ID

are these correct? please feel free to comment or suggest. i do apologize for the inconvenience. i am just a newbie and i need to finish thi project. i appreciate your response. thank you in advance.

 

[neileg]

Shouldn't the Previous contract data be in its own record? I would expect you to have a record for each contract so that you retain the history for that worker.

 

[lushh]

Re: table structure issue

thanks for the quick reply. what do you mean by record for each contract? should i create a separate table for the contract alone? sorry, i'm not that familiar yet with databases.

 

[neileg]

Your 3rd table has data about the position including the contract start and end. I assume that you may have a series of contracts so it would make sense to keep track of each contract. It may be appropriate to have a new record in this table for each contract if the position data changes each time. If not, it may be better to have a contract table that links to the 3rd table on the position ID.

 

[lushh]

ok. i get it. other than the contracts table, is everything alright? am i on the right track?