-2

I am attempting to build a database for a company's payroll system. I have worked on an ERD and my main concerns revolve around the loops, reimbursement table and possible weak entities.

I haven't done much of these and could use your help.

Thanks.

Link to ERD: ERD for Payroll

Stephan
  • 39
  • 10
  • Have you got any description of requirements? And what is the difference between employee and user? – Ryan Gadsdon Oct 04 '17 at 07:55
  • An employee is simply an employee in the company. The user table is the login information for each employee. An employee would log into the system and fill out their timesheet. After, the timesheet would have to be approved and then a payroll sheet would have to be generated. There is a lock off date and time for which timesheets are allowed to be submitted. Deductions may be made from an employee's weekly salary if they owe money to the company. Reimbursements may be made to an employee if the company owes the employee money. – Stephan Oct 04 '17 at 10:58
  • ok, also whats the significance of timesheet approval and timesheet_detail? – Ryan Gadsdon Oct 04 '17 at 11:08
  • A timesheet has multiple days of work each to be stored as a different record. I created timesheet_detail to avoid the replication of data under a single timesheet_Id in timesheet table. Timesheet approval refers to the approval of a timesheets by the co-ordinators, management and the accounts department. Each new record stored in this table will be a new approval by each department. I got that idea from this answer: https://stackoverflow.com/a/3283449/8549542 – Stephan Oct 04 '17 at 13:29
  • Please edit clarifications into your question, comments are ephemeral. Please [use text whenever possible, not images/inks](https://meta.stackoverflow.com/q/285551/3404097). – philipxy Oct 06 '17 at 09:37

1 Answers1

1

I would probably change the user to user_login. and remove loan_payment and reimbursement and just simply have a payment table. This could hold similar values but also include a payment type that would include its values like 'reimbursement' or 'work payment' because like you said reimbursement doesn't need to be its own table.

Ryan Gadsdon
  • 2,272
  • 4
  • 31
  • 56
  • One employee should have one username. Should I just put username and password in the employee table? – Stephan Oct 04 '17 at 13:31
  • @Stephan oh sorry i mis-read that. Yes i would put username and password in employee table. I remember a lecturer always advised me if you have 3 or less columns for a table then its likely it doesnt need to be a table – Ryan Gadsdon Oct 04 '17 at 13:44
  • @Stephan upload a revised copy if you have one and i can take a look – Ryan Gadsdon Oct 04 '17 at 13:44
  • I uploaded an updated ERD. I took your advice regarding the merger into one payment table, but the payment table having reimbursements while linked to a loan table has me kind of uneasy as in it doesn't feel like it should be there. – Stephan Oct 04 '17 at 22:43
  • 1
    ah i see what you mean. I would look at having reimbursements as a sub class of payment – Ryan Gadsdon Oct 05 '17 at 08:11
  • Alright so I created the parent-child entities as seen in the updated ERD. I put the LOAN_ID in the deduction table since it is not a common attribute. – Stephan Oct 05 '17 at 20:02
  • I would look to add more fields to the payment table such as payment number, payment amount etc – Ryan Gadsdon Oct 06 '17 at 08:07
  • I can merge the two amount attributes and form a common payment attribute, but t wouldn't that leave the reimbursement table with only one attribute? – Stephan Oct 06 '17 at 11:10
  • you could add more to reimbursement but generally sub-classes inherit some columns from the main class – Ryan Gadsdon Oct 06 '17 at 11:44
  • I want to confirm if it's okay to have that one attribute? Because the only difference between a reimbursement and a deduction is that a deduction is linked to a loan – Stephan Oct 06 '17 at 17:24
  • Yeah, thats fine. I would suggest putting the database together with mock data and test it with queries. Only this way will show you what you need to change or improve – Ryan Gadsdon Oct 11 '17 at 09:39
  • Doing that for a few days now and I've worked out some kinks. I think I have it covered for now. Thank you very much for your help. – Stephan Oct 11 '17 at 23:26
  • @Stephan Ok thats great. Let me know if you want me to look at anything else. Could you do me a favour and if i put an answer could you upvote it and tick it please. Looking to improve my rep on here. thanks :) – Ryan Gadsdon Oct 12 '17 at 08:39