Hello Nelson, Le 2014-09-04 à 10:39, Nelson Green <nelsongreen84@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
