Creating relationship table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating relationship table

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-03-25 : 06:58:21

I have a table:
Persons (PersonID, Name)

How do I create a table to show 2-way relationships
For instance, if john is related to smith, then it also means that smith is also related to john.

Any idea how I would do it WITHOUT duplicating the data.

For example I can have a table as:


PersonID   RelatedPersonID
1           2
2           1

But that is actually duplicating the data. Is that the only approach, or is there any better on?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-25 : 07:48:18

Post your full table structure

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-25 : 08:05:41

Create a single table that holds one row per relationship:

CREATE TABLE Relate(Person1 int NOT NULL, Person2 int NOT NULL,
CONSTRAINT CHK_OneLessTwo CHECK(Person1<Person2),
CONSTRAINT FK_Person1_Persons FOREIGN KEY(Person1) REFERENCES Person(PersonID),
CONSTRAINT FK_Person2_Persons FOREIGN KEY(Person2) REFERENCES Person(PersonID),
CONSTRAINT PK_Relate PRIMARY KEY(Person1, Person2))

To show both directions of a relationship, create the following view:

CREATE VIEW TwoWay AS
SELECT Person1 AS PersonID, Person2 AS RelatedPersonID FROM Relate
UNION ALL
SELECT Person2 AS PersonID, Person1 AS RelatedPersonID FROM Relate

You would then insert the related persons data into the Relate table, and then use the view to query for those relationships.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-25 : 08:11:58

Because "related person" is so vague, there is no indication as to how data should be stored in your table so it could be stored either way. If you can make 'related person' more specific -- i.e., "parent" or "dependant" or something like that -- then it will be clear exactly how the two entities are related and if data is stored properly then you shouldn't have a situation in which you have two opposite entries.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources