Database Design Question

[birdman]

Over the years I have developed a number of different databases for a company that I work for. Although the databases could have been related, they have not been for a variety of administrative reasons (namely, inter-departmental power struggles). I have recently convinced the powers to be that ideally all of the systems should be integrated together and am trying to keep the momentum rolling. My problem lies in the design of the database structure as there is one piece of information I am not certain how to setup.

The system starts as an employee personnel system and expands into tracking employee status/history, dependents, employee attendance/sick days/vacation, employee benefits, and employee benefits used. The last two items refer primarily to travel benefits granted to employees in one of our remote offices. The use of the benefits has to be tracked so that they do not claim too many free trips.

My uncertainty lies in how to incorporate the fiscal year component into the equation. Afterall, in any one fiscal year there are many employees, and yet an employee is around (one hopes) for many fiscal years. Also benefits change from one fiscal year to a next, as does vacation time granted, dependent ages etc

[Kurt Knippel]

quote:

---

Originally posted by birdman:

The fiscal year can be important for a variety of reasons. An example would be the Personel Dept. wish to keep a record of past years sick days and holidays as they can be accumulated if unused. In the case of benefits, often times the invoices for travel benefits only come in after a new fiscal year has started so the system has to be able to differentiate between two different fiscal years.

---

Ok, in the case you site the actual FY has no real bearing on the data. You have to keep track of the number of days outstanding. If the person gets 5 days per year and they have 8 available, then 3 of those were from the previous FY.

You can flag this data with the FY, but this would not be anything more than simply another piece of data associated with the item.

I think that perhaps you have convinced yourself that the FY is more significient than it really is.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

[birdman]

quote:

---

Originally posted by captkurt:

I think that perhaps you have convinced yourself that the FY is more significient than it really is.

---

You could be absolutely right. I agree that for attendance/sick days and vacations the fiscal year can be easily incorporated.

I believe that it was the benefits case that was giving me the angst. Perhaps I was trying to eliminate having to repeat the fiscal year information in different places. I will think on it some more with pen and paper.

[Kurt Knippel]

quote:

---

Originally posted by birdman:

Over the years I have developed a number of different databases for a company that I work for. Although the databases could have been related, they have not been for a variety of administrative reasons (namely, inter-departmental power struggles). I have recently convinced the powers to be that ideally all of the systems should be integrated together and am trying to keep the momentum rolling. My problem lies in the design of the database structure as there is one piece of information I am not certain how to setup.

The system starts as an employee personnel system and expands into tracking employee status/history, dependents, employee attendance/sick days/vacation, employee benefits, and employee benefits used. The last two items refer primarily to travel benefits granted to employees in one of our remote offices. The use of the benefits has to be tracked so that they do not claim too many free trips.

My uncertainty lies in how to incorporate the fiscal year component into the equation. Afterall, in any one fiscal year there are many employees, and yet an employee is around (one hopes) for many fiscal years. Also benefits change from one fiscal year to a next, as does vacation time granted, dependent ages etc

[birdman]

The fiscal year can be important for a variety of reasons. An example would be the Personel Dept. wish to keep a record of past years sick days and holidays as they can be accumulated if unused. In the case of benefits, often times the invoices for travel benefits only come in after a new fiscal year has started so the system has to be able to differentiate between two different fiscal years.