Subject: Data Model for Employees Savings Funds


Note that we have marked Entries in bold to help us create the Data Model.

Subject: Database Model
Question: To whom it may concern.-

I work at a Bank that provides financial services and amongst them, we manage Employees Savings Fund
which is completely optional (for the Companies) but is becoming (80 %) as an almost mandatory fringe benefit.

I would really appreciate if you could help me constructing a Data Base Model for a Employees Savings Fund,
it's something like a Payroll but has some differences:
1.- We have to assume that the company already has this fringe benefit and makes deposits on behalf of two parties:
for each dollar the Company gives, the employee gives another equally, therefore instilling workers the habit of saving.

2.- All employees have the right to participate but there are (at least) 2 (two) kinds of employees: Union and White collar.

3.- The employees can withdraw the accrued savings twice a year.

4.- The employees can (optionally) ask for loans from the savings (the limit is their accrued amount to the date when they
ask the loan)

5.- The savings yield some interest by investing the money in a Bank (where I work) and by the interests generated by the loans.

6.- The company usually fires / or the employee quits his / her job and can (not optional) take his / her accrued amount to date
but, (this one is optional) the company can choose to keep the interest yielded by that particular employee's amount and then
divide it equally among the rest of the employees.

7.- There's 2 criteria to divide the interests generated by the fund: Number of days worked and the amount accrued across the year,
it has to be proportionally divided (50 - 50) since it's not the same for a person who worked 365 days to someone who's been working
for the last 3 months and the same goes to the person who accrued 1,000 to someone with 100.

8.- Sometimes people quit and some companies have the policy of letting people come back, they (the company), for simplicity, use
the same Employee number they had when they left, but his is not mandatory.

9.- The law makes a distinction between the 2 withdrawals I mention in point No. 3, but they have different purposes, the first
(usually made in June) is considered a "loan" without interests and the second, the liquidation of the fund, since the first is
optional, there must be a differentiation between the 2.

10.- The Company makes deposits matching the sum of all contributions, and has to match the calculation of such contributions but
sometimes the company makes mistakes and the aount deposited does not match the calculation so I need an "adjustment" field where I
can state that there was an error and it can be rectified.

11.- It would be great if a "history" of the career of the employee can be kept since one can be promoted and can change departments
and therefore getting a salary increment.

So far, I have inferred the following entities: ----------------------------------
Employee (Employee_Id, Last_Name, Names, Hiring_date, SSN): SSN and Employee_Id are my candidates for PK
(Note.- some companies do not provide SSN for security reasons) ----------------------------------

Loan --------------------------------- Quit / Depart / Fired ---> I still don't know what to call this but implies that the person
is leaving the company and the amount has to be subtracted form the total. ---------------------------------

Liquidation ---> The person is not leaving the company, this is just with the purpose of starting a new cycle. ---------------------------------

Withdraw ------------> The employee decides to withdraw (in June) the money or just part of it with no interest charged.
---------------------------------
Loan Payment ----> The employee starts to repay his / her loan.
--------------------------------

I hope this is enough for you to help me creating the model.

Thanks in advance.