0

I'm currently making a payslip system wherein there are two tables, the account_table and the payslip_table. I'm currently having trouble with the database design, I'm torn between creating a relationship between two tables or not creating one at all. I guess I'll first explain how this system works:

  1. Admin logs in to the site
  2. Admin goes to the upload payslip page
  3. Admin uploads an excel file and the back end code parses the excel file and saves it in the database
  4. User logs in to the site
  5. User goes to the payslip page
  6. Payslip page shows the row where the employee_id is the session_user(since a user's username is his employee_id)

The account table has the following columns:
1. employee_id
2. password
3. first_name
4. last_name
5. user_type

The payslip table has the following columns:
1. payslip_id
2. employee_id
3. salary
4. tax
5. total_deductions
6. total_salary

The requirements for this system is that the owner wants to upload a payslip even if there "isn't an account for a user".

Q. Then why are your tables designed like that?
A. The payslip in its nature has an employee_id and its presence is the key on determining which payslip owns which. For me to effectively show a payslip, all I need to do is to compare the session user (which I have mentioned is their employee id since it's their username) with the employee_id on the payslip table and just echo the row that was hit.

Q. What happens to the payslips when a user isn't an employee anymore? There will be useless rows on the payslip_table
A. I've decided to create a column named created_on and add a triger to delete payslips that are 3 months old (since they have no use already)

Q. What happens to an account when a user isn't an employee anymore?
A. Admins have the authority to delete a user, once a user is gone from the company, the admins can terminate the account and as mentioned above, the payslips of the deleted user will be eventually deleted

Q. Why go through all this trouble?
A. The owner specifically stated that he wanted to have the payslips ready even if an account has yet to be made so if one person were to create an account, his payslips are automatically ready for viewing

I have very minimal knowledge in database designs and I'm very open to suggestions.

Or if you guys could suggest an alternative way of achieving the requirement using foreign keys then that would be the best way to go.

Jordan Care
  • 15
  • 2
  • As much as I wanted to create a relationship between the two tables, there isn't really anything I can do because I can't create a relationship pointing to a non existent field. – Jordan Care Feb 02 '15 at 13:10

1 Answers1

0

You should create a relationship between the two, as you can see here you will be able to add a null value into a foreign key field and fill it later with an employee_id.

This will help with finding rows, and making sure there is no useless data floating about wasting space.

The only problem I see is how the admin will link payslips already on the system to a new employee account. e.g. finding the correct payslips for the new employee number

Community
  • 1
  • 1
Grushton94
  • 603
  • 1
  • 7
  • 17
  • Although your solution still leaves a problem, I do appreciate the extra knowledge about null fields on the foreign key. – Jordan Care Feb 02 '15 at 13:29
  • the problem is there either way you choose to do this, it is a client problem. i just wanted to bring it to light. you may want to talk to them about it. – Grushton94 Feb 02 '15 at 13:31
  • You could make it easier on them, do something like make a dummy employee that is attached to the payslips and they just fill out the details later. That seems like the most user friendly – Grushton94 Feb 02 '15 at 13:33
  • Fair enough, I'll first try explaining that if the client wants it that way, there are bound to be problems. If all else fails, I can tell them your way. Thank you – Jordan Care Feb 02 '15 at 13:59