MRG (wh**@who.com) writes:
I have a geographic points table containing point records with latitude
and longitude points. I also have several other tables containing
entities that will relate to points in the points table, ie.
individuals, organizations.
In every case, there will be a many to many relationship between an entity
table and the points table. Normally, this means creating an associative
table for each relationship consisting of two columns; one FK pointing to
the PK of the points table table, another FK pointing to the PK of the
entity table.
In SQL server, I like taking advantage of the cascade deletes. If a record
from either the points table or the entity table is deleted, then their
relationships will also be deleted from the associative table.
I would like to know if anyone knows of a solution that would combine
all of the relationships into one table, eliminating the need for a
table for each relationship.
You could do:
CREATE TABLE relationships (
relationshipid int NOT NULL,
latitud latitud_type NOT NULL,
longitude longitude_type NOT NULL,
individid int NULL,
organizationid int NULL,
...
CONSTRAINT pk_rships PRIMARY KEY NONCLUSTERED (relationshipid)
CONSTRAINT u_rships UNIQUE CLUSTERED
(latitude, longitude, individid, organizationid, ...),
CONSTRAINT fk_rships1 FOREIGN KEY (latitude, longitude)
REFERENCES (points) WITH CASCADE DELETE,
CONSTRAINT fk_rships2 FOREIGN KEY (individid)
REFERENCES individuals (indvidid) WITH CASCADE DELETE,
CONSTRAINT fk_rships3 FOREIGN KEY (organizationid)
REFERENCES organizations (organizationid) WITH CASCADE DELETE,
...
CONSTRAINT ckt_rhips CHECK (1 =
CASE WHEN individid IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN organziation IS NOT NULL THEN 1 ELSE 0 END +
...)
)
But if this does not look appetizing to you, I am in complete agreement.
From what you have described I would certainly go with multiple tables.
If the situation is really dire, I might consider introducing a
supertype, and the relation would be to that supertype. The
various entities would then be subtypes of that supertype.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
