Database Table/s for Employee Information (need your opinions)

[dev.rogee]

hi.

i need some opinions on a certain matter...

normally, i create an employee table containing:

employee ID
first name
middle initial
last name
nationality
birthday
age
home address
home phone
mobile phone
email address
employment status
date started work
salary
civil status
etc etc etc

..the thing i would like your opinions on is..

scenario:
i would like to split the information into:

basic info (employee ID, first name, middle initial, last name)
personal info(...)
contact info(...)
employment info(...)
and so on...

of course, all tables can be linked through the employee ID..


does it affect the application's performance, having several tables just for an employee's information? of course, sometimes all the info will be needed, so all tables will be accessed, but there are also times that only one table at a time will be accessed... i'm just not used to creating my employee table in this manner, but i was asked to do it that way.. so i'm here asking for your opinions..

thanks!

[szlamany]

Breaking it up into several tables is a much better design.

If you are using a modern and professional database (like MS SQL) then speed will not be affected.

You can have start and end dates for some of these "child tables" - so that employees can leave and return and move from one department or position to another.

You can create VIEW's that join lots of these tables together so that it almost appears that they are in the "first" format you described.

What is the backend DB?

[dev.rogee]

Thank you for that.

well, right now, i'm working with an MS Access database, but when i get my official computer, i'll migrate to MS SQL, because the supposed server would be running the application with MS SQL.

[szlamany]

There is a free version of MS SQL called SQL SERVER EXPRESS that you can download from the MSDN site.

It's meant to run on local workstation's - although it can also be used as a shared database.

You might want to consider jumping right to that - since it's 100% compatible with SQL SERVER and easy to migrate.

[dev.rogee]

whoa.. that's cool...

i should take a look at that.. thank you very much!

by the way,

You can have start and end dates for some of these "child tables" - so that employees can leave and return and move from one department or position to another.

...that's a great idea.. i'm going to make use of that...

i'll get back as soon as i've tried it.

umm... Breaking it up into several tables is a much better design ...can you give me a little more information as to how it makes a better design? just so that i'll understand better.. thanks!

[szlamany]

Here's an example of a POSITION table from our HR system (I've removed some fields - but you should get the idea).

Use AcctFiles
GO
DROP TABLE Position_T
Go

CREATE TABLE Position_T
(MasId		int
,StartDate	datetime
,EndDate	datetime
,Division	varchar(4)
,PayGroup	varchar(4)
,JobCode	varchar(4)
,Location	varchar(4)
,UnionCode	varchar(2)
,FTE		money
,Salary		money
,Grade		varchar(4)
,Step		varchar(2)
,constraint	    PKPosition
		    PRIMARY KEY (MasId, StartDate)
)
GO

This child table can have several rows for an Employee. Only one row has a NULL ENDDATE - that is the current open position.

Some of the odd datatypes (varchar(4) for instance) are due to our migration from a mainframe legacy system.

Start designing your tables - I'll be more than happy to give you constructive criticism.

Is this a school project?