On Thu, Sep 4, 2014 at 9:48 AM, François Beausoleil <franc...@teksol.info> wrote:
> Hello Nelson, > > Le 2014-09-04 à 10:39, Nelson Green <nelsongree...@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 > > Thanks François. This is pretty generic stuff, but my first reading has got me thinking that I should at least pick up some ideas from it. I will give it a go. And I've got a new web site that I've now known about before, so thanks for that as well. Regards, Nelson