CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-design
emily_bma
emily_bma

Reputation: 301

How to introduce new concept into database table structure for 0..1 instances?

I have an issue I've encountered with new requirements and don't know how best to structure my database tables. The situation is basically as follows:

I have employees stored in an Employees table. Due to an increase in production, the company has had to hire many temporary employees. All of these employees, both permanent and temporary employees, have to enter data into an ERP system. So, essentially, employees will be entering "Orders" with the Orders table having a field named "EnteredBy". In an ideal world, I would have both of these two types of employees stored in the Employees table.

Due to the company's different systems in place and groups within the company not talking to each other, some temporary employees do become permanent employees, so we need a way to report on all of the Orders that "new" permanent employee has done while they were "temporary employees" and technically "merge" the data that the temporary employee worked on to their new permanent employee record.

Not all temporary employees will become permanent employees. All orders have to have an "employee" attached to it, i.e. a Foreign Key holding the Employee's unique identifier.

I am stuck on how to incorporate the concept of a "temporary employee" into this data model and still upholding data integrity with Orders. Any suggestions?

Current DB table structure:

Employee

Order

UPDATE 1: When trying to convey this problem in a database table design was when I decided to seek the advice from the SO experts. In my analysis of a solution via OOP, a design of a [TempEmployee] IS A [Employee], but I wasn't able to wrap my brain on how to convey that into table design and the CRUD operations. FYI, an [Employee] has many fields while a [TempEmployee] has a subset of those fields, thus all of its fields existing in an Employee.

Upvotes: 2

Views: 134

Answers (2)

StuartLC
StuartLC

Reputation: 107317

(I'm assuming that it is possible to load Temporary employees as employees)

Inheritance is a fairly common scenario in modelling - in your case, it seems that can extend the existing Employee structure, so you can model temporary employees as follows:

TemporaryEmployee
 - EmployeeID (PK and FK to Employee)
 - StartDate
 - EndDate
 - Other temp employee fields here

Because TemporaryEmployee IS also an Employee, it shares the Employee primary key, and referential integrity can be enforced via making TemporaryEmployee.EmployeeId a foreign key to Employee.EmployeeId.

The integrity of Orders is unaffected, since the existing Employees-Orders RI is unchanged. Another benefit of extending rather than changing is that you haven't changed any of the underlying model, so regression issues caused by your change to your ERP system should be avoided (although testing will still be essential).

Edit, clarification:

Existing ERP System tables:

CREATE TABLE Employee
(
   EmployeeID INT identity(1,1) NOT NULL PRIMARY KEY,
   EmployeeTypeId ? -- e.g. might be able to repurpose to identity Temporary employees?
   -- Other Employee Fields here ... not all of these will be relevant to TemporaryEmployee
);

CREATE TABLE Orders
(
   OrderId INT identity(1,1) NOT NULL PRIMARY KEY,
   EmployeeID INT NOT NULL FOREIGN KEY REFERENCES Employee(EmployeeID)
   -- etc.
);

Now you extend Employee with another table, without changing the above schema at all:

CREATE TABLE TempEmployee
(
   EmployeeID INT NOT NULL PRIMARY KEY,
   -- Other extended fields here as per above

   CONSTRAINT FK_TempEmployee_Employee REFERENCES Employee(EmployeeID)
);

Upvotes: 4

Jacob Lambert
Jacob Lambert

Reputation: 7679

You could add a status field on your Employee table that denotes whether the employee is full time or part time. Create another table like:

create table temp_change_date (
    employeeID int PK (FK),
    change_date date
);

Populate this table with an update trigger on your Employee table when an employee goes from temporary to permanent.

This way the Orders automatically map and you can see all the orders created while they were a temp employee using the date they were changed.

Upvotes: 0

Related Questions