0

I have two different types of employees for my database design, temporary and permanent. Temporary are paid by the hour whilst permanent employees are paid by a salary. My idea is to implement them by having an overall employee table which contains the NI number, email etc which describes them as an employee but have different salary tables for each one.

Though, I'm bent right now if I should just create two entirely new tables for Temporary staff and permanent staff members as that. As many of the temporary employees often are reoccurring my original supposed idea sounded ideal to the scenario but relooking at the design it seems almost messy to have.

If possible, could someone provide me with some constructive criticism on my methodology and if I should just separate the two types of employees into their own tables? To help understand I've posted the tables below

tempStaffPay

TempRoleID (PK)
Employee(FK) 
HoursAssigned
Role (as there's two types of roles a temporary staff member may have) 
PayRate

PermanentStaffSalary

PermanentID (PK)
EmployeeID (Fk) 
Salary

EmployeeRole

EmployeeType (PK)
EmployeeID*
TempRoleID*
PermanentID*

Employee

EmployeeID (PK)
First Name
Last Name
NI number
Employee Type * 
Phone
Address
Email
Julius Velvet
  • 43
  • 6
  • I don't see much benefit in having two entirely separate tables - your design looks fine to me - keep everything common in `Employee`, have an `EmployeeRole` table to specify whether it's a permanent or temporary employee, separate out the specifics for each type into a separate "add-on" table - I would leave it like this – marc_s Feb 17 '19 at 18:02
  • Thank you for the quick response, though I'm not sure if I understand what you mean, is my design for the employeeRole needing additional work or is everything correct with how I laid it out? – Julius Velvet Feb 17 '19 at 22:07
  • What do the asterisks (*) mean? What do the `StaffID` foreign keys refer to? What is the purpose of the `EmployeeRole` table? Is `Employee Type` in `Employee` and `EmployeeType`in `EmployeeRole` related? – Jon Heggland Feb 21 '19 at 09:11
  • The astricks mean foreign keys, my apologies for not stating before but the foreign key for Employee Type means either if they're a permanent employee or a temporary. Relooking at this, I think I should combine EmployeeType and EmployeeID to create a compound key rather than rely on the primary key of employeetype – Julius Velvet Feb 21 '19 at 18:09
  • Please clarify via edits, not comments. When giving PKs & FKs say what column set they are. When giving FKs say what column list in what table references what column list in what table. PS This is a faq in both 'many FKs to many tables' & 'database/sql subtyping/inheritance" senses. For the latter, the former is an antipattern. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. – philipxy Jun 21 '19 at 21:58
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jun 21 '19 at 22:00

0 Answers0