Re: Employee modeling question

[Robin <robinstc@xxxxxxxxxx>]

Robin St.Clair

On 04/09/2014 20:44, Nelson Green wrote:

On Thu, Sep 4, 2014 at 9:48 AM, François Beausoleil <francois@xxxxxxxxxxx> wrote:

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

 

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

I strongly suggest you read the writings of Joe Celko, he has been addressing this kind of problem for decades. Ultimately, it is very important not to confuse behaviour with entities (yes employment is behavioural)

Cheers