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.

Thank you
Glenn

MRG (what@who.com) writes:
[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

Hi Glenn,

I just answered this question in microsoft.public.sqlserver.programming.
Please don't multi-post!

Best, Hugo
--