Re: Employee modeling question

From: Nelson Green <nelsongreen84(at)gmail(dot)com>
To: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: Re: Employee modeling question
Date: 2014-09-04 19:44:50
Message-ID: CAGo-KZkm_ny_MTr5CEAFdfrhX0Mif+z4Aujagi3F65bcoSXHXA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, Sep 4, 2014 at 9:48 AM, François Beausoleil <francois(at)teksol(dot)info>
wrote:

> Hello Nelson,
>
> Le 2014-09-04 à 10:39, Nelson Green <nelsongreen84(at)gmail(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robin 2014-09-04 19:56:23 Re: Employee modeling question
Previous Message Kevin Goess 2014-09-04 18:08:28 Re: free RAM not being used for page cache