table normalisation

[pronate]

Hello,

this is a question on table design.

I want to do a database for leave application. I have 2 tables, one is the information on the employee and linked to another one to record the leave details.

In the leave details, there are different types of leave to apply. Eg, Annual leave, Medical Leave, Maternity Leave, Paternity Leave....

Question:
Should i have a field to store leave type and another to store number of day....or

Should i define a separate fields for each type of leave to store number of days?

Does the second option comply better to the normalisation rule?

Thanks
Sam

 

[KenReay]

Hi

You should have

"a field to store leave type and another to store number of day..."

This probably implies a third table to hold the valid list of leave types and it also means of course that any given employee with have n leave records wheer n is 0 - whatever




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jrbarnett]

Sam,

The first one is more normalised, as the second will have lots of null values in it.
I would have a table of leave types, then leave linking

Leave type, employee ID, no. days

with a composite primary key of leave type and employee ID.

Instead of storing the number of days you could put in start and end date, so that you end up with a leave history for each employee over the time period, which can be totalled at the end.

John

 

[lespaul]

Have you read the following article?

http://www.alphabetcitydataworks.com/Developers/FundamentalsOfRelationalDatabaseDesign.doc

It does a great job walking you through normalization.

Leslie

 

[willir]

pronate

Actually, although the leave type is important to what you want to do, you really want a "transaction" file that tracks leave activity...

tblHistory - track leave activity and leave type
HistoryID - primary key, autonumber
EmployeeID - foreign key to employee table
LeaveType - text field, see comments
DateOut - date field
DateIn - date field
Comments - Memo field - document issues
+ anything else specific to the leave of absence


LeaveType
You may want a table to store the types of leave. Or you can just store the information in a list associated with a combo or list field on the data entry form. Probably best to have a Leave Type field -- a little flexibility, plus can be used to store other information such as payroll codes, unemployement eligibility, max number of days allowed (ie, short term vs long term disability).

tblLeaveType
LeaveCode - primary key, text field, code used for leave, eg STD, MAT, PAT (paternity leave?? - UK or Canada??)
LeaveDescription - text field
Plus other info; consider
- LeaveRestriction
- MaxDays - maximum allowed days
- InsuranceCode - code use by insurance company

An alternative to tblLeaveType
LeaveID - autonumber, primary key
LeaveCode - text, unique
plus the rest...

This variation uses an ID number instead of the code as the primary key. This would allow you to more easily change the leave code without impacting the data. If you use this alternative, then you may need to change the tblHistory...

LeaveCode -> LeaveID


The employee table will be fairly straight forward...

tblEmployee
EmployeeID - autonumber, primary key
LName - last name
FName - first name
SSN / SIN, etc...

You may want to have a check box that indicates if the employee is or is not on leave. Kind of breaks a rule on normalization but makes the screen a little more friendly.


Next question,
How to display the leave activity and find out the current status?

You have a many-to-many realtionship between employee and leave type....
- An employee can go on different types of leave
- Many employees can be on the same leave type

A many-to-many relastionship requires a joining or intermediary table ... but ... your leave history is this table.

Leave history can be displayed in a continuous form; perhaps embed the continuous form as a subform in the employee form.

Current status can be found by finding the most recent date. This can be displayed as a "status" unbound text field on the employee field.


What is not covered...
This solution may not be adequate for absences per a union contract; specifically incidences.
This solution would need to be tweaked if tracking hours as well as days. i.e., track tardiness, suspension, etc.

But after reading Paul Litwin's article, along with this info, you should have some ideas on where you want to go.

Richard