Employee modeling question

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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@teksol.info <mailto:francois@teksol.info>> wrote:

Hello Nelson,

Le 2014-09-04 à 10:39, Nelson Green <nelsongreen84@gmail.com
<mailto: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

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

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

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Sep 5, 2014 at 9:46 AM, John McKown <john.archie.mckown@gmail.com>
wrote:

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

Thanks for the warning John. No, I hold no illusion that I will completely
solve this problem through two days of reading. What I meant is that I will
have two days away from work to get some work done. What I hope to have by
the start of next week is a better idea of what I am up against. I am glad
that I realized this simple problem will require a complex solution before
I got too far along, and I really appreciate the learning opportunities
that have been offered here.

Nelson

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Sep 5, 2014 at 11:39 AM, Rich Shepard <rshepard@appl-ecosys.com>
wrote:

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.

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

Thank you Rich, and apologies for the delay in getting back to this.
Sometimes
my job has a bad habit of getting in the way of getting work done.

I've been through the first four or five chapters of the SQL For Smarties
book,
and I've glanced at the other two books we have, but I didn't find anything
especially enlightening (and I was surprised at the number of typographical
errors in the content). I have also read through the other references I was
given.

Although I have not completely hashed this whole situation out, I am leaning
towards an exclusivity constraint on department and business, where one of
the
columns will be required to be null, and a check constraint on the business
column that will not allow businesses that are referenced in the department
table. This seems to meet all of my rules and requirements, and will also
work
in the case of external contracts applying to a business or a department.

If this plan changes dramatically I will update this posting, and I do
appreciate the advice that I received from you and everyone else. I
especially
appreciate being given pointers to information sources as opposed to
receiving
pat answers without explanations. Reading and learning will prove much more
beneficial in the long run.

Well, back to work. Gotta go explain to someone why two separate and
unrelated
tables won't model their multi step workflow too well (OK not at all
really). I
just love how people that can populate a spreadsheet think that makes them
into
data professionals.

Nelson