Good morning,
Hopefully this is the correct place to ask this type of question.
I am in the early stages of designing a system to track employee
information, including some aspects of their payroll, one of which is
the source of the salary funds within the business. I need to make
this generic enough to accommodate instances where an employee's
salary is sourced from a department's budget, as well as those
salaries of employees that are employed by companies too small to be
departmentalized. So when an employee is employed by a department, the
department is the source of the employee's salary, and the business is
the department's parent entity. But when an employee is employed by a
business that has no departments, the business entity is the source of
the employee's salary, and the parent entity. I am struggling with the
correct logical implementation of this scenario.
So basically I have a situation where an employee will most likely be
associated with a department within a business, but this can not be
guaranteed, and I'm not 100% sure how to handle this. I am going to
face the same problem with contracts where a department can out-source
a function, or a business can. I think there may even be instances
where a business with departments may out-source functionality that is
not charged to a department.
I could store the department and business attributes with the
employee, but without proper constraints the referenced department
could conceivably not correspond to the referenced business. Or I
could ensure that all businesses have at least one department,
defaulting to the business when the business has no department, but
then I'd be storing duplicate data. The other alternative I've come up
with is an exclusive constraint where the employee instance can only
reference a department or a business, but not both.
None of these solutions seems ideal, although the exclusivity solution
seems like it would work the best, and I have had to create
exclusivity constraints before. So, am I missing a more obvious
solution, or am I even on track here?
Thanks in advance and regards,
Nelson