Basic DB Design principles for designing new staff/hr database

[whitespace]

Hello all,

I'm an expereinced data analyst that's looking at developing a database to record all our staff/posts/post history/assets etc. We already have an old Access DB but it's such a mess - anyway I've been drawing up some table and relationship plans and would just like to ask (I'm sure) a basic question:

I have drawn up obvious tables e.g. Employee, Post, PostHistory, Asset and understand the relationships I need to make e.g. 1 Employee to many PostHistory and 1 Post to many PostHistory; 1 Employee to many Asset - etc. (As it's possible for one employee to have/have had many posts and it is possible for one post to belong to many staff)

Anyway - in creating the Employee table I have a lot of columns say regarding the contact details (tel no. fac no. email, etc.) - is it generally adviseable to put these in a seperate table?? If so what sort of link would it be. The reason is I can;t help thinking they aren't directly linked to Employee details but then in a way they are - as there's only 1 set of contact details per employee?

Sorry to go on.. Any help would be much appreciated.

Many thanks

Jenny

 

[KenHigg]

The first thing you should do is make a list of everything you want the databse to do from a business perspective. Be careful not to put 'nice' to have things in the list. The list should look something like:

- Record current employee information
- Employee information is:
1. Name
2. Address
3. City
4. Current job
5. Current job start date
6. Previous jobs
7. Previous job start and end dates

- Maintain this same information on employees no longer with the company
- Have a current list of possible positions with information like pay, division, etc

etc, etc

Then after you have a very well defined set of minimum requirements you can have a 'nice to have' list of items like maybe where the employee used to work, a way to track performance reviews, etc.

Hope all of this makes sense. The point being if you don't get this first part nailed down before you even create a .mdb then chances are you'll put in a lot of work creating stuff only have to go back and do a lot of rework because you didn't think of something earlier...

 

[whitespace]

Thanks a lot Ken. I've just had a meeting and it looks like it might be a lot more complex than I initially thought. We need to keep track of honorariums, secondments, employee history (for previous grades/scale points), post history (who was assigned previously to posts) blah blah!

Are there any access mdb templates that anyone knows of that are a sort of standard staff/employee database to work with? I've looked on the microsoft site but not found any - I thought this would be quite a standard and commonly needed database?

Many thanks for any help.

Jenny

 

[Banana]

If you go to Microsoft's site and search for templates, they should have a few.

Another suggestion, after have had a look at those templates is to get yourself a whiteboard, a pile of sticky notes then try to diagram the relationship with "actual data" on the post notes.

 

[KenHigg]

I'm sure there are some real simple ones out there like in Northwinds but I wouldn't count on be able to leverage much of it for your use. I think the key is to get your long term table model correct. Then you can develop it in small chunks and add in features over time...