July 14, 2009 at 6:26 pm
Hello can someone provide some advice please.
I have a name table with a clientnumber, title, given name , surname which has a list of names.
what would be the best way of creating a relationship between records..eg record1 is the brother of record 10
would it be best to create another table called relationship and include
clientnumber, relatedto, relationshiptype
thanks
Fred
July 14, 2009 at 9:13 pm
Yes, making a relationship similar to the one you mention is a good way to go. It's certainly a better way than adding columns in your name table. You have to decide whether or not to relate every name to itself too. That makes some queries easier, others odder. Depends on how you use the relationship table.
July 15, 2009 at 12:00 am
thanks for the reply.
can you explain why it would be advantageous to relate each record to itself.
I am not really sure what you mean
thanks
Fred
July 15, 2009 at 12:55 am
Hello,
If there can be only one relation at most you can create self referencing table like
CREATE TABLE Clients (
clientnumber int IDENTITY(1,1) NOT NULL,
title nvarchar(100),
[given name] nvarchar(100),
surname nvarchar(100),
brotherClient int
CONSTRAINT PK_Clients PRIMARY KEY CLUSTERED (clientnumber)
)
GO
ALTER TABLE Clients ADD CONSTRAINT FK_Clients_Clients FOREIGN KEY(brotherClient) REFERENCES Clients (clientnumber)
GO
But since one person can have more than 1 brother, so a mapping table should be used.
But I do not think a self referencing row in the map table should be useful.
Because a person is not the brother of himself or herself.
Adding a record for every client would require additional controls.
Eralper
July 15, 2009 at 6:41 am
Assuming that you're working with SQL Server 2008 since you posted to this forum, you could also look at using the Hiearchy data type. That's for establishing Parent/Child type of relationships, but it might work for sibling style relationships as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 15, 2009 at 7:00 am
I am using sql 2008...is this easy to do..are there any examples i can work with?
thanks
Fred
July 15, 2009 at 7:31 am
frecal (7/15/2009)
I am using sql 2008...is this easy to do..are there any examples i can work with?thanks
Fred
It's very easy to do. Look it up in the Books Online for examples. There may also be an article or two here at SSC.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply