Should my delete stored procedure account for cascading to FK tables?

Question

Say I have a Employees table (PK is employeeID) and a sales table, where the sales table has a FK column for employeeID.

Now when designing my deleteEmployee stored procedure, should I first delete rows in the Sales table or should I create seperate stored procedures to delete in each table, and then worry about that in my business logic layer?

Answer 1

No, no, no.

Add a field to the Employee table called "OutServiceDt" and store the date they left your company.

That way you keep the records of the sales they made, their address information, and whatever else you've got :)

If you're looking for employes who are in service, filter on "OutServiceDt is null".