A question of design

Hi,
My apologies if this isn't strictly the right group but you've always been
very helpful in the past so...
I was wondering if there were any best practices with regards to the
following scenario:
Given a simple FK relationship (employees table to transaction table for
example), is there a best method for catering for employee information that
wouldn't appear in the employees table - for cample contractors. I.e. given
a transaction which included salaried employees (who would appear in the
employee table as designed) and 3rd party contractors who wouldn't, I'm not
certain as the best way to handle this. I can't simply include a
"Contractor" employee as each employee is unique to a transaction.
I'm thinking of "breaking" the FK relationship so that in the transaction
table, for a contractor a standard value could be used ("C1", "C2", "C3",
"Cn"), for an employee their ID is entered as usual and enforcing RI through
sprocs and triggers. However I'm concerned about whether this approach is a
"valid" one.
I hope I've managed to convey this clearly enough, any advice, assistance or
replies in general are gratefully recieved.
Kind regards
Chris.In my opinion you should have separate table for employees and separate for
contractors. then you will not be forced to 'tweak' relationships. in
general, employees and contractors seem to be separate entity types.
Peter|||"Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
news:ej4Vs81RGHA.5656@.TK2MSFTNGP11.phx.gbl...
> In my opinion you should have separate table for employees and separate
> for contractors. then you will not be forced to 'tweak' relationships. in
> general, employees and contractors seem to be separate entity types.
> Peter
>
Peter,
Thanks for your view. After looking at this, I've pretty much came to the
same conclusion, in that the Transaction table will reference an "Employee"
table and a "Contractor" table - both of which allow zero / one to many
relationships. For querying, I'm imagining a COALESCE between the two sets
of data...
Again, your thoughts on this (specifically using the COALESCE command to
combine the data when querying) would be greatly appreciated.
Thanks again
Chris.|||Chris Strug wrote:
> Hi,
> My apologies if this isn't strictly the right group but you've always been
> very helpful in the past so...
> I was wondering if there were any best practices with regards to the
> following scenario:
> Given a simple FK relationship (employees table to transaction table for
> example), is there a best method for catering for employee information tha
t
> wouldn't appear in the employees table - for cample contractors. I.e. give
n
> a transaction which included salaried employees (who would appear in the
> employee table as designed) and 3rd party contractors who wouldn't, I'm no
t
> certain as the best way to handle this. I can't simply include a
> "Contractor" employee as each employee is unique to a transaction.
> I'm thinking of "breaking" the FK relationship so that in the transaction
> table, for a contractor a standard value could be used ("C1", "C2", "C3",
> "Cn"), for an employee their ID is entered as usual and enforcing RI throu
gh
> sprocs and triggers. However I'm concerned about whether this approach is
a
> "valid" one.
> I hope I've managed to convey this clearly enough, any advice, assistance
or
> replies in general are gratefully recieved.
> Kind regards
> Chris.
If employee id is optional then you can put it in another table and
only populate it when relevant:
CREATE TABLE employee_transactions
(trans_id INTEGER NOT NULL PRIMARY KEY
REFERENCES transactions (trans_id),
emp_id INTEGER NOT NULL
REFERENCES employees (emp_id));
I'm not clear whether or how you want to track contractor ids however.
Posting your existing DDL and sample data might help make it clearer.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Please send some sample data and resired result then :)
Peter