469,090 Members | 1,092 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

Multiple many to many

MRG
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
Jul 20 '05 #1
2 2027
On Tue, 29 Jun 2004 09:32:44 GMT, MRG wrote:
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


Hi Glenn,

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

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2
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
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

66 posts views Thread by Darren Dale | last post: by
6 posts views Thread by Ben Hallert | last post: by
32 posts views Thread by tshad | last post: by
2 posts views Thread by Howard | last post: by
2 posts views Thread by Diego | last post: by
15 posts views Thread by iKiLL | last post: by
7 posts views Thread by =?Utf-8?B?TG9zdEluTUQ=?= | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.