October 26, 2002 at 8:35 am
I have a case of circular relations that I am not quite able to model. I wonder if someone can point me in the right direction.
I have a company which has agreements which has policies which has benefits. To the company are employees related via employments. Each employment has a one relation to a benefit belonging to a policy belonging to an agreement belonging to the company the employment is related to. (hard to explain in words, have a picture but found no way of posting it).
--Create the tables
CREATE TABLE Agreements (
Agreement varchar(20) NOT NULL,
Company varchar(20) NOT NULL)
go
ALTER TABLE Agreements
ADD PRIMARY KEY (Agreement)
go
CREATE TABLE BenefitDomain (
Benefit varchar(20) NOT NULL)
go
ALTER TABLE BenefitDomain
ADD PRIMARY KEY (Benefit)
go
CREATE TABLE Benefits (
Benefit varchar(20) NOT NULL,
Policy varchar(20) NOT NULL)
go
ALTER TABLE Benefits
ADD CONSTRAINT BenefitsUQCO UNIQUE (Benefit,Policy)
go
CREATE TABLE Companies (
Company varchar(20) NOT NULL)
go
ALTER TABLE Companies
ADD PRIMARY KEY (Company)
go
CREATE TABLE Employees (
Number int NOT NULL)
go
ALTER TABLE Employees
ADD PRIMARY KEY (Number)
go
CREATE TABLE Employments (
Number int NOT NULL,
Company varchar(20) NOT NULL,
Benefit varchar(20) NOT NULL,
Policy varchar(20) NOT NULL)
go
ALTER TABLE Employments
ADD CONSTRAINT EmploymentsUQCO UNIQUE (Number,Company)
go
CREATE TABLE Policies (
Policy varchar(20) NOT NULL,
Agreement varchar(20) Not NULL
)
go
ALTER TABLE Policies
ADD PRIMARY KEY (Policy)
go
--Foreign keys
ALTER TABLE Benefits
ADD CONSTRAINT Benefits1FK FOREIGN KEY (Benefit) REFERENCES BenefitDomain(Benefit)
go
ALTER TABLE Benefits
ADD CONSTRAINT Benefits2FK FOREIGN KEY (Policy) REFERENCES Policies(Policy)
go
ALTER TABLE Employments
ADD CONSTRAINT Employments1FK FOREIGN KEY (Number) REFERENCES Employees(Number)
go
ALTER TABLE Employments
ADD CONSTRAINT Employments2FK FOREIGN KEY (Company) REFERENCES Companies(Company)
go
ALTER TABLE Policies
ADD CONSTRAINT PoliciesFK FOREIGN KEY (Agreement) REFERENCES Agreements(Agreement)
go
ALTER TABLE Employments
ADD CONSTRAINT Employments3FK FOREIGN KEY (Benefit, Policy) REFERENCES Benefits(Benefit, Policy)
go
--Insert data
insert into companies values('Volvo')
insert into companies values('Saab')
select * from Companies
insert into employees values(10)
insert into employees values(20)
select * from employees
insert into agreements values ('Agree Volvo', 'Volvo')
insert into agreements values ('Agree Saab', 'Saab')
select * from agreements
insert into policies values ('Polic Volvo', 'Agree Volvo')
insert into policies values ('Polic Saab', 'Agree Saab')
select * from policies
insert into benefitdomain values ('Benefit Rich')
insert into benefitdomain values ('Benefit Poor')
select * from benefitdomain
insert into benefits values ('Benefit Rich','Polic Volvo')
insert into benefits values ('Benefit Poor','Polic Saab')
select * from benefits
Now this insert is OK:
insert into employments values (10,'Volvo','Benefit Rich','Polic Volvo')
This insert is not OK since this employee gets the wrong company's benefit
insert into employments values (20, 'Volvo','Benefit Poor','Polic Saab')
How does one model this correct?
October 29, 2002 at 8:00 am
This was removed by the editor as SPAM
October 29, 2002 at 12:47 pm
I've recreated the structure as defined. What I see is:
1. there is a many-to-many relationship between BenefitDomain and Policy. You've expressed that relationship with the Benefits 'linking' table. However, as currently constructed there is the potential to express that relationship via the Employments table. In my opion, if the only purpose served by the Benefits table is to express that many-to-many relationship, then you can drop it and use the Employments table to express that relationship (BenefitsDomain to Employments.Benefit and Policy to Employments.Policy). On the other hand, if the Benefits table actually contains additional fields that more completely describe 'benefits', then it should have a primary key added and that new primary key should be used in conjunction with the appropriate foreign key in Employments instead of the current relation between Benefits and Employments.
2. Let's ignore Employees and Employments for the moment, and focus on the implied relationship between Company and BenefitDomain (the end-points of the chain of relationships). Following the chain of one-to-many relationships (in the direction flowing from the 'one' side to the 'many' side) from Company, we eventually arrive at Benefits. Doing likewise with Benefit Domain also leads to termination at Benefits. Thus, even though there are intermediaries, there is a many-to-many relationship between Company and BenefitDomain that is expressed via the Benefits 'linking' table. In other words, having identified a particular record in Benefits it is possible to follow the chain of relationships back upwards (from the 'many' side to the 'one' side) to discover a single record in each of BenefitDomain, Policy, Agreement, and Company. Now let's bring Employees and Employments back into the discussion. You have currently specified that a many-to-many relation exists between Benefits and Employees that is expressed via the Employments 'linking' table. Since we've already determined that we can discover the company that 'owns' a particular Benefit-Policy combination, the relation between Benefits and Employments is sufficient to express the many-to-many relationship that exists between Employee and Company, although there are several intervening tables.
Conclusion: I would get rid of the Company field in Employments (and the relation, of course!), thus leaving the relationship between Company and Employee as an implicit relation expressed through the chain of relations. This would remove 'Company' from your insert statements, thus making it impossible to create invalid combinations of Company and Benefit within the Employments table. Depending on the actual use of the Benefits table, I would either add a primary key and use that to form a relation with Employments (thus leaving Employments with only 2 fields, Number and BenefitID) or drop the Benefits table and create my relations directly between Benefits and Employments and between Policy and Employments.
NOTE: under your current structure, following the chain of relationships as described in 2 (above) lead to the conclusion that there is a simultaneous relation between each employee and TWO companies. Since your description of the problem indicates that such a relation is actually not valid, then it seems that one of those relations must go. If each company was permitted only one agreement, then you could safely remove the 'benefits relation' and associated fields, leaving only the 'company relation'.
I hope that I properly understood the problem as was able to provide help, if only by getting you to think about other possibilities.
Ron Porter
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply