Re: Employee modeling question - Mailing list pgsql-general

Hello Nelson,

Le 2014-09-04 à 10:39, Nelson Green <nelsongreen84@gmail.com> a écrit :

> 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?

I’ve found this « Universal Person and Organization Data Model » very useful to understand complex questions like that: http://www.tdan.com/view-articles/5014

Hope this helps,
François Beausoleil