473,396 Members | 1,789 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

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 2176
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

66
by: Darren Dale | last post by:
Hello, def test(data): i = ? This is the line I have trouble with if i==1: return data else: return data a,b,c,d = test()
6
by: Ben Hallert | last post by:
Hi guys, I'm trying to figure out what bone headed mistake I made on something I put together. I've got a form (named 'context') that has a variable number of select-multiple inputs on it. ...
2
by: Will | last post by:
I have a table, tblManinstructions with fields Code & InstructionID, one Code can have many InstructionID. I also have tblinstructions (fields instructionID & instruction). What I want to do is...
32
by: tshad | last post by:
Can you do a search for more that one string in another string? Something like: someString.IndexOf("something1","something2","something3",0) or would you have to do something like: if...
9
by: Abhishek Srivastava | last post by:
Hello All, In IIS 6.0 We have a concept of worker processes and application pools. As I understand it, we can have multiple worker process per appliction pool. Each worker process is dedicated...
2
by: Marcus | last post by:
I have seen many posts of people with the same problem as me (attached below), but I have yet to see any solutions posted. Has anyone figured out how to deploy an Asp.net web site to the webserver...
2
by: Howard | last post by:
how would you design a table that allows an item to belong to multiple parent categories. single parent example: select * from table1 where parentid = 5 in this case parentid is an indexable...
2
by: Diego | last post by:
Hi everybody! I'm using DB2 PE v8.2.3 for linux. I've defined a database with the following schema: ANNOTATION(ID,AUTHOR,TEXT) ANNOTATION_BOOK(ANNOTATION_ID,OBJECT_ID)...
15
by: iKiLL | last post by:
hi all, I would like to be able to create an umbrella class for all my main global sections but I would still like to keep them all in separate file something like the below but I keep getting...
7
by: =?Utf-8?B?TG9zdEluTUQ=?= | last post by:
Hi All :) I'm converting VB6 using True DBGrid Pro 8.0 to VB2005 using DataGridView. True DBGrid has a MultipleLines property that controls whether individual records span multiple lines. Is...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.