DataBase Structure

  • 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

  • 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.

  • 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

  • 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

  • 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

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

    thanks

    Fred

  • 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