DataBase Structure

Question

DataBase Structure

frecal

Ten Centuries

Points: 1094
More actions
July 14, 2009 at 6:26 pm

#212439

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ksullivan SSCrazy Points: 2346 More actions · Answer 1

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.

frecal Ten Centuries Points: 1094 More actions · Answer 2

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

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 3

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

T-SQL and SQL Server

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

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

frecal Ten Centuries Points: 1094 More actions · Answer 5

I am using sql 2008...is this easy to do..are there any examples i can work with?

thanks

Fred

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

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