- the employee has an effective date and an expiration date
- the employee is assigned to a department
- each department has an effective date and an expiration date
- the employee is assigned a badge with an effective date and an expiration date
- the department is the top level item, then employee, and finally badge
In the real application each field also has a bit field called Blocked to temporarily shut off the ability to use each item. These bit fields are Nullable, which is wrong. Blocked shouldn't affect the effective dates.
If you deactivate the top level item it should inactivate all child items. When an item is deactivated it sets the expiration date to today.
There is also a rule that says effective date cannot be after the expiration date - sounds like bad news for these dates:
After deactivating the Department Employee and Badge will generate the error message "End date cannot be before start date" and the effective dates are out of order for employee and badge: