Обсуждение: Employee modeling question

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?

Thanks in advance and regards,
Nelson

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

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

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

 

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

On Fri, Sep 5, 2014 at 7:52 AM, Nelson Green <nelsongreen84@gmail.com> wrote:
>
> Thanks Robin. Ironically enough, our little local library has three books by
> Joe Celko, so looks like I may have a weekend of reading ahead of me.

I'm impressed that your library has him. But, unless you are _very_
good with SQL, you're going to need more than a single weekend. Unless
by weekend you mean 48 hours of constant reading. I have a lot of his
books. They are excellent. They are _not_ for beginners. The "For
Smarties" portion is not just a play against  the "For Dummies"
series. Joe does some high powered SQL. I am not, and never really
will be, an SQL expert. I love his books. But, at least for me, they
are an intense read.

>
> Nelson
>



--
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown

On Fri, 5 Sep 2014, John McKown wrote:

> They are excellent. They are _not_ for beginners. The "For Smarties"
> portion is not just a play against the "For Dummies" series. Joe does some
> high powered SQL.

   I read Joe Celko's columns in Database Advisor and a couple of other
magazines in the '80s, and have read most of his books.

   For the purpose of developing an employee schema with departments for
some, his "SQL For Smarties" provides very sound advice on how to proceed.
Having separate company, department, and employee tables is a given. But,
you might need many-to-many tables to keep track of the complex
relationships. This is all covered in the chapters on DDL (Data Definition
Language) and is separate from the chapters on DML (Data Manipulation
Language).

Good luck,

Rich