Posted by Hugo Kornelis on 10/01/06 11:17

On 30 May 2005 09:39:26 -0700, martind-crap1@mailblocks.com wrote:

>Hi,
>
>What is the best way to model this: Assume I have two objects: Agency
>and Publisher, and both have a 1-to-n relationship to Employee. This is
>a true 1-to-n relationship, as each Employee can only work for one
>Agency or one Publisher. Let's assume further that I cannot introduce a
>supertype (e.g. Employer) which holds the 1-to-n relationship.
(snip)
>What do you guys consider best practice in this situation?

Hi Jen,

I'm with David - the best solution is to defy the assumption and to
introduce the supertype table Employer.

But if I suspend my disbelief and assume that there actually is a valid
reason for not doing that, than I'd go with

CREATE TABLE Employees
(SSN char(9) NOT NULL,
AgencyCode char(4) DFEFAULT NULL,
PubNum int DEFAULT NULL,
..... (other columns),
PRIMARY KEY (SSN)
FOREIGN KEY (AgencyCode) REFERENCES Agencies (AgencyCode)
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (PubNum) REFERENCES Publishers (PubNum)
ON DELETE NO ACTION ON UPDATE CASCADE,
CHECK ((AgencyCode IS NULL AND PubNum IS NOT NULL)
OR (AgencyCode IS NOT NULL AND PubNum IS NULL))
)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]