Defining a domain relationship with data out of the domain...

Hi,

I'm trying to figure this one out and was wondering if there were any best
practices I should be looking at

I have a typical Domain relationship, e.g. employees table (PK) linked to a
transaction table (FK) where a transaction may have none, one or several
employees. This is fine as far as it goes - each transaction may have
several (unique) employees.

However, the system also has to cater for contracted employees. I.e. a
transaction may include X employees and Y contractors. The problem is that
the requirements of the system mean that each contractor cannot be included
into the employee table and the fact that an employee may only be used on
each transaction once means that I can't simply create a "contractor"
employee.

Assuming that I've managed to explain that clearly enough, my problem
amounts to: Given a lookup table, how do I design my db so that information
outside of that lookup table can be included into the referencing table?

I'm writing this in SQL Server but I figure the principals are much the
same.

Any and all advice is gratefully received.

Thanks

Chris.

Dear Chris:

First, the basics. Is this "typical Domain relationship" what we call
many-to-many? "Each transaction may have several employees." Is it also
the case that each employee may be involved in several transactions? If so,
have you created a junction table for this? That should enforce the
uniqueness of both transaction and employee in the junction.

As for contracted employees, you say "each contractor cannot be included
into the employee table." Are the attributes of contractors and employees
so different this cannot be done? This does create a complexity.

I suppose one could have a separate junction table for contractors.
Depending on the unique keys for employees and contractors, it may not be
possible to consider both employees and contractors.

I would have preferred to keep employees and contractors together in one
table with a simple attribute to separate them when needed, but to be able
to enforce uniqueness of a key between them. Perhpas the loss of this
uniqueness is the substance behind your difficulty. However, one could
still UNION the two sets using some common columns, and including a Source
column specifying from which table the rows came.

Please comment on the above and let me know how I can better understand the
problem in working toward a solution.

Tom Ellison


"Chris Strug" wrote in
message . uk...
Hi,

I'm trying to figure this one out and was wondering if there were any best
practices I should be looking at

I have a typical Domain relationship, e.g. employees table (PK) linked to
a transaction table (FK) where a transaction may have none, one or several
employees. This is fine as far as it goes - each transaction may have
several (unique) employees.

However, the system also has to cater for contracted employees. I.e. a
transaction may include X employees and Y contractors. The problem is that
the requirements of the system mean that each contractor cannot be
included into the employee table and the fact that an employee may only be
used on each transaction once means that I can't simply create a
"contractor" employee.

Assuming that I've managed to explain that clearly enough, my problem
amounts to: Given a lookup table, how do I design my db so that
information outside of that lookup table can be included into the
referencing table?

I'm writing this in SQL Server but I figure the principals are much the
same.

Any and all advice is gratefully received.

Thanks

Chris.

Tom,

Thank you for your reply and my apologies in not replying sooner. You're
correct in that the relationship is a many to many and that I have created a
junction table - my question was really referring to the junction table -
employee table.

Its not a question of the data that contractors cannot be stored as
employees, more the practicalities of the business make such a solution
infeasible. The same issue prevents contractors being stored in their own
table.

In the week or so since I initiallty posted this I have done some reading
and thinking and the solution that I have came to to keep the domain "many
to one" relationship of the employee - junction table albiet allowing nulls
into the Employee_ID FK field to allow for a many to one / none
relationship. However I have included a contractor field in the junction
table which simply holds a reference indicating a contractor. The process is
that for a standard employee, the employee ID is entered into the employee
field in the junction table and the contractor field is set as NULL. For a
contractor, the reverse is the case - the employee_ID FK field is set to
NULL and the contractor field includes a reference indicating a contractor.

Of course this requires some fairly rigid business rules and constraints
(for example, the employee_ID and contractor fields in the junction table
must have a value in either one of the fields but not both and a value must
be specified) but after some testing it does appear to work. SQL Server
coalesce command seems to handle linking to the table and general data
retrieval.

I fully expect this solution to be bit of a bodge job and violate all kinds
of design best practices however it does appear to work (albiet not in a
terribly elegant way).

However your thoughts on this are gratefully received and your time is
appreciated!

Thank your once again.

Chris.

"Tom Ellison" wrote in message
...
Dear Chris:

First, the basics. Is this "typical Domain relationship" what we call
many-to-many? "Each transaction may have several employees." Is it also
the case that each employee may be involved in several transactions? If
so, have you created a junction table for this? That should enforce the
uniqueness of both transaction and employee in the junction.

As for contracted employees, you say "each contractor cannot be included
into the employee table." Are the attributes of contractors and employees
so different this cannot be done? This does create a complexity.

I suppose one could have a separate junction table for contractors.
Depending on the unique keys for employees and contractors, it may not be
possible to consider both employees and contractors.

I would have preferred to keep employees and contractors together in one
table with a simple attribute to separate them when needed, but to be able
to enforce uniqueness of a key between them. Perhpas the loss of this
uniqueness is the substance behind your difficulty. However, one could
still UNION the two sets using some common columns, and including a Source
column specifying from which table the rows came.

Please comment on the above and let me know how I can better understand
the problem in working toward a solution.

Tom Ellison


"Chris Strug" wrote in
message . uk...
Hi,

I'm trying to figure this one out and was wondering if there were any
best practices I should be looking at

I have a typical Domain relationship, e.g. employees table (PK) linked to
a transaction table (FK) where a transaction may have none, one or
several employees. This is fine as far as it goes - each transaction may
have several (unique) employees.

However, the system also has to cater for contracted employees. I.e. a
transaction may include X employees and Y contractors. The problem is
that the requirements of the system mean that each contractor cannot be
included into the employee table and the fact that an employee may only
be used on each transaction once means that I can't simply create a
"contractor" employee.

Assuming that I've managed to explain that clearly enough, my problem
amounts to: Given a lookup table, how do I design my db so that
information outside of that lookup table can be included into the
referencing table?

I'm writing this in SQL Server but I figure the principals are much the
same.

Any and all advice is gratefully received.

Thanks

Chris.

Chris Strug wrote:

the requirements of the system mean that each contractor cannot be included
into the employee table

Why? If contractors and employess have similar or identical attributes,
it makes sense to put them in the same table. If you sometimes need an
employee-only view, or a contractor-only view, you just have two stored
queries on that table.


and the fact that an employee may only be used on
each transaction once means that I can't simply create a "contractor"
employee.

That doesn't follow at all. From what you have said, one transaction
can have many employees. The primary key of the table containing that
data, would be TransNo + EmpNo (or somesuch). This immediately prevents
the use of the same EmpNo, more than once, for the same TransNo. It's
irrelevant whether those EmpNos are employees, contractors, or any
admixture thereof.

Yes? No?

TC (MVP Access)
http://tc2.atspace.com

Strongly agree.

Tom Ellison


"TC" wrote in message
ups.com...

Chris Strug wrote:

the requirements of the system mean that each contractor cannot be
included
into the employee table

Why? If contractors and employess have similar or identical attributes,
it makes sense to put them in the same table. If you sometimes need an
employee-only view, or a contractor-only view, you just have two stored
queries on that table.


and the fact that an employee may only be used on
each transaction once means that I can't simply create a "contractor"
employee.

That doesn't follow at all. From what you have said, one transaction
can have many employees. The primary key of the table containing that
data, would be TransNo + EmpNo (or somesuch). This immediately prevents
the use of the same EmpNo, more than once, for the same TransNo. It's
irrelevant whether those EmpNos are employees, contractors, or any
admixture thereof.

Yes? No?

TC (MVP Access)
http://tc2.atspace.com

The Dream Team !!

TC (MVP Access)
http://tc2.atspace.com

Or nightmare on Database Avenue?

Tom Ellison


"TC" wrote in message
oups.com...
The Dream Team !!

TC (MVP Access)
http://tc2.atspace.com

When Relations Go Baaaad !

(OP, we're just joking around, no insult intended to you!)

TC (MVP Access)
http://tc2.atspace.com

Dear TC:

I believe database is the one place where you can pick your relations.

Tom Ellison


"TC" wrote in message
ups.com...
When Relations Go Baaaad !

(OP, we're just joking around, no insult intended to you!)

TC (MVP Access)
http://tc2.atspace.com

Tom, there is absolutely no way that I can top that one!

You have won this exchange !!

But my time will come !!!

Cheers,
TC (MVP Access)
http://tc2.atspace.com