Jump to content
Sign in to follow this  
Tom R.

Advice on schema for personnel database

Recommended Posts

Hi -

I am working on a personnel database for the law firm I work for. In addition to using it to track employees, they want to track job applicants. They didn't specify that it needed to be a single database, but I think it may be a good idea to do it that way. One consideration is the possibility of multiple terms of employment, for instance a person might work for a few months as a summer intern and then return in a year or more to work as an attorney.

So, I'm thinking of using the following tables (also see attached picture)

  • People - basic info, name, SSN, etc., with related tables for address & phone number
  • Employees - with details of employment, e.g. position, salary, hire date & termination date, etc., and where people is a one to many relation to Employees. Maybe Employment would be a better name, since one person could have multiple employments.
  • Candidates - similar to Employees/Employment, but with details regarding the job applied for; also a container field to store resumes.

Another thing I need to keep track of is which states a lawyer is licensed in, what courts she is admitted to, and any other professional organizations. So I thought I might use a "type" field in an Organizations table to cover those possibilities and then use a Membership table as a join between Organizations and People.

One thing I'm not sure of is whether to base the main layout for Employees on the People table ( and use portals for Employment details), or base it on the Employees/Employment table ( and use portals to display the Person details ).

Other than the layout question, I don't really have any other particular question. But if anyone has done something similar, I'd be glad to hear any advice or suggestions.

Thanks a lot,

Tom

post-86592-0-94375400-1343607106_thumb.p

Share this post


Link to post
Share on other sites

Hi Tom R

Personally I would consider keeping all of the people records in the same table, differentiating different types of contacts with a Type field.

Keeping them all in one place, gives you certain advantages e.g. if at some point you need to report or search through all of your contacts together then they are handily in once table ready for you...

Then you just needs separate layouts to display different types of people showing the relevant fields for that type of user. Then I'd have employment as a separate table so that you can have employment records that start and end between 2 dates as related records.

Vicky

Share this post


Link to post
Share on other sites

I think you have a good plan - the People/Candidates/Employees setup sounds perfect to me. I like 'Employment' better than 'Employees' though. As I was reading, when I first came the the 'Employees' section, I thought you were referring to a 1 to 1 relationship with People table, based on it's name. I also think you should reconsider the naming of 'Candidates' table for the same reason. One person can apply for different positions over time, or even the same position over and over again. I think of a Candidate as a person, which is why I don't think that name fits here. Maybe 'Applications' is a more fitting name?

Where will you store email addresses? I would assume you'd want them in a related table like you have phone numbers. I usually use a 'ContactInfo' table that has 3 data fields:

category (email, phone, fax)

description (home, work, cell, etc.)

data (the email address, phone number, or fax number)

One thing I'm not sure of is whether to base the main layout for Employees on the People table ( and use portals for Employment details), or base it on the Employees/Employment table ( and use portals to display the Person details ).

Both methods are valid in the right scenario, and I think you are the only one that has enough information to determine which is best to use in your situation. I think it's going to depend on what the information in the database is used for - which will determine how users will want to access it - which will help you determine which table to base the main layout on.

  • Like 1

Share this post


Link to post
Share on other sites

Vicky & Dan, thanks for your comments. I'm still new enough at all of this that I sometimes like to get a second opinion.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.