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

C

Chris Strug

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.
 
T

Tom Ellison

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
 
C

Chris Strug

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.
 
T

TC

Chris said:
the requirements of the system mean that each contractor cannot be included
into the employee table
Click to expand...

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.
Click to expand...

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
 
T

Tom Ellison

Strongly agree.

Tom Ellison


TC said:
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.



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
Click to expand...
 
T

Tom Ellison

Dear TC:

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

Tom Ellison
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Two tables, one link of date, multiple entries in one table per da 11
Lookup needs to fill two fields 2
Workflow - Work Distribution 1
Intricate Relationship.... 6
"Circular" Relationship 3
Many to Many with AutoNumber PK 4
unique values with multiple criteria 4
Relationship query needs answering! 6

Top