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

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" <tellison@xxxxxxxxxxx> wrote in message
news:uYaGa04RGHA.224@xxxxxxxxxxxxxxxxxxxxxxx
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" <hotmail@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:fCxRf.35732$wl.33109@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.





.

Relevant Pages

  • Re: Why you need a registry cleaner
    ... However your computer is still on the companies network with a potential of damaging the companies network probably from the inside. ... No malware, at least none that I know, can tell the difference between employee and contractor. ... Many companies will not simply take the word of an employee or contractor the computer is clean, that assumption has brought down many networks when it turned out to be false. ... And they pushed a version of the corporate virus scanner onto my ...
    (microsoft.public.windowsxp.basics)
  • Re: Culling the Singapore security industry
    ... I was told by a contractor that he lost his business completely after his ... > Likewise, in the pass, security guards used to work only 8 hour a day, on ... >>> security guard are often suck in by these small agencies offering ... employee or potential guards need to work at least 12 hours a ...
    (soc.culture.singapore)
  • Re: Atlas Shrugged Off
    ... back at work as a contractor the following Monday. ... Employers typically have costs to pay for an employee, ...
    (borland.public.delphi.non-technical)
  • Re: Contractor or Employee (was Who Pays for Damage ???)
    ... I followed the reports that came out of the Illinois Film Commission's efforts. ... But if you are provided a camera and told how to set it up, and what framing for what shots, then your work is under their "Control", and you are their employee. ... independent contractor if, in fact, they are an employee in the eyes of the ...
    (rec.arts.movies.production.sound)
  • Re: Why you need a registry cleaner
    ... EMPLOYEE). ... If the grunts could get the work done, ... I am a contractor, and the laptop in question is mine. ... The Sys Admin had no right to query/audit my registry and turn the ...
    (microsoft.public.windowsxp.basics)
Loading