What shape should a db like this take?

[rsmccli]

We are making a new personnel db. Lots of employees, about 10 companies (contractors). I just found out the bosses want the contractor companies to be able to remotely add lists of their employees to the db. I was wondering if it would be a better idea to have one big table with all employees in it and a "temporary" table that held new additions (for oversight by central personnel). Or should I make separate tables for each company, then just create a query/datasheet form that would show all of the companies/employees together for use in the personnel centre.


All ideas welcome; having trouble visualizing the best way to go about this.

See also: http://www.thescripts.com/forum/thread773373.html
AC2002 btw

rsmccli

[cori25]

My suggestion to you is to not allow them direct access to the database. That could potentially cause numerous problems, it is always best to keep the backend monitored by 1 person.

I would create a spreadsheet for each company on a shared drive, only give access to the individual who will be inputting the changes/additions to the employees. Keep in mind that the only changes that would occur would be if an employee is hired, fired or promoted.

Once you have this set up and all the employees are in the spreadsheet, link them into the database.

I would create a union query to pull all the data together and then go from there.

Hope this helps

[Scott Price]

In addition to what has already been suggested: you have a simple One to Many relationship between the two subjects mentioned: Employees and Companies. One Company can have many Employees, but One employee can only have One company.

There isn't any trouble than using one table for the Employees, containing a foreign key reference to the company they work for. This foreign key is the primary key field of the Companies table.

If you have one employee on the payroll of more than one company, then you've got problems :-) Not as far as the database part is concerned, just from the personnel standpoint...

Regards,
Scott

[jaxjagfan]

We are making a new personnel db. Lots of employees, about 10 companies (contractors). I just found out the bosses want the contractor companies to be able to remotely add lists of their employees to the db. I was wondering if it would be a better idea to have one big table with all employees in it and a "temporary" table that held new additions (for oversight by central personnel). Or should I make separate tables for each company, then just create a query/datasheet form that would show all of the companies/employees together for use in the personnel centre.


All ideas welcome; having trouble visualizing the best way to go about this.

AC2002 btw

rsmccli

1. Is your company on a network?
2. Do the contract companies have access to your network?
a. If so are you already sharing data and/or files with the contractor?
b. If not does your company have the ability to give the contract companies VPN access to the network.
3. Have you considered data and network security issues?

This should probably be an EDI project. The contract companies would send you update files in a pre-specified layout. You would import the updates into your database. (I.E. They would send you EXCEL files for you to import.)

Be cautious when exchanging employee/contractor data. Some companies use SSN as EmployeeID. ID theft is a huge issue now days - your company could get a huge fine and you and the boss would be fired most likely.

[rsmccli]

Thanks for the replies guys, that helps me out alot.

In addition to what has already been suggested: you have a simple One to Many relationship between the two subjects mentioned: Employees and Companies. One Company can have many Employees, but One employee can only have One company.

There isn't any trouble than using one table for the Employees, containing a foreign key reference to the company they work for. This foreign key is the primary key field of the Companies table.

If you have one employee on the payroll of more than one company, then you've got problems :-) Not as far as the database part is concerned, just from the personnel standpoint...

One reason why I was thinking about splitting the dbs into separate company tables is because of the fact that some of these employees will be entered for more than one company because there is some crossover between them. They arent neccessarily working for that company at this time, but it is more of a list of people that have worked for them at some point over the last 5 years or so. So yeah, thats why I wanted to have a Yes/no field called "current" to know which company that they are currently employed by. Anyway, I'll figure it out somehow. Appreciate the help.

[Scott Price]

Not a problem!

In the case of multiple employees showing as working for multiple companies, the accepted way of handling this is to break it down into three tables:

Employees, Companies and Empoyee/Company. This removes the Many to Many relationship and creates two separate One to Many relationships. This is a far better way to structure the db than splitting each company into it's own separate table.

Regards,
Scott

[rsmccli]

Not a problem!

In the case of multiple employees showing as working for multiple companies, the accepted way of handling this is to break it down into three tables:

Employees, Companies and Empoyee/Company. This removes the Many to Many relationship and creates two separate One to Many relationships. This is a far better way to structure the db than splitting each company into it's own separate table.

Regards,
Scott

Ok, I'll give it a try. Thanks.