locked
Maintaining Family Relationship to avoid Circular Reference in Data RRS feed

  • Question

  • I have a table relationshipmaster with relationid as smallint and relationname as varchar(20). The values are as follows,

    1 PARENT
    2 SPOUSE
    3 CHILD
    4 SISTER

    I have another table to store names and its relationship with other names.

    detailtable

    =======

    id        smallint

    name  varchar(100)

    relationship smallint           FK of relationshipmaster(relationid )

    relationshipid smallint        FK of detailtable(id )

    How can I stop the circular reference or irrelvant data as follows,

    i)

    X CHILD OF Y

    Z CHILD OF X

    Y CHILD OF Z

    ii)

    X CHILD OF Y

    Y SISTER OF Z

    Z SPOUSE OF X

    Pls help

    Thanks in advance

    mohamed


    • Edited by ynainamoh Sunday, November 25, 2012 6:55 AM
    Sunday, November 25, 2012 5:53 AM

Answers

  • To start with, there are a few pecularlities in your tables. All columns
    beside the id columns are NULL. What does NULL in
    relationshipmaster.relationname mean? That here is a some kind of relation,
    but we don't know what it is?

    In the same vein detailtable.name is NULL. Is this to handle persons of
    which we don't know the name? We know that Paul got a son when he was very
    young, but the name of the son is forgotten?

    The table names are also confusing. relationshipmaster does not seem like a
    master table to. Rather it is a lookup table that defines different relation
    types, and there is not much about details in the detailtable.

    Anyway, for your actual problem, you need a trigger that runs a recursive
    CTE with cycle detectetion. The easiest way, I think, is to build a path and
    if an id you arrive to already exists in the path, you have a cycle. Below
    is an example. The table variable that appears is not needed as such, but
    limitations in T-SQL syntax prevents us from using a CTE in an IF statement.

    CREATE TRIGGER cycle_tri ON detailtable AFTER INSERT, UPDATE AS

    DECLARE @cycles TABLE (cycle varchar(MAX) NOT NULL)

    ; WITH rekurs AS (
        SELECT relationid,
               path = convert(varchar(MAX), ' ' + ltrim(str(relationid)) + ' '),
               iscycle = convert(bigint, 0)
        FROM   inserted
        WHERE  relationshipid = 1
        UNION  ALL
        SELECT d.relationid, path + ltrim(str(d.relationid)) + ' ',
               iscycle = charindex(' ' + ltrim(str(d.relationid)) + ' ', path)
        FROM   rekurs r
        JOIN   detailtable d ON r.relationid = d.id
        WHERE  d.relationshipid = 1
          AND  r.iscycle = 0
    )
    INSERT @cycles(cycle)
       SELECT path
       FROM   rekurs
       WHERE  iscycle > 0

    IF EXISTS (SELECT * FROM @cycles)
    BEGIN
       ROLLBACK TRANSACTION
       RAISERROR ('Cycles detected in the data!', 16, 1)
    END
    go
    INSERT detailtable(name, id, relationid, relationshipid)
      VALUES ('Paul',    1, 2, 2),
             ('Richard', 2, 1, 2)
    go
    INSERT detailtable(name, id, relationid, relationshipid)
      VALUES ('Jack', 11, 12, 1),
             ('Jill', 12, 11, 1)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Sunday, November 25, 2012 3:33 PM
    • Marked as answer by Kalman Toth Friday, November 30, 2012 4:10 PM
    Sunday, November 25, 2012 1:35 PM

All replies

  • Hi,

    Make a parent child relationship .

    Make function which can identify it's relationship name. 

    For any query tell me.


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Sunday, November 25, 2012 6:57 AM
  • Could you pls help me by elobrating the functionality(function)?
    Sunday, November 25, 2012 7:24 AM
  • For quick assistance post CREATE TABLEs and INSERT INTOs (sample population). Also post any code you have. Thanks.

    Tree processing example:

    http://www.sqlusa.com/bestpractices2005/organizationtree/


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Sunday, November 25, 2012 10:47 AM
  • --Table CreationCREATE TABLE [dbo].[relationshipmaster]([relationid] [smallint] NOT NULL, [relationname] [varchar](20) NULL,CONSTRAINT [PK_relationshipmaster] PRIMARY KEY CLUSTERED ([relationid] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

    CREATE TABLE [dbo].[detailtable]( [id] [smallint] NOT NULL, [name] [varchar](100) NULL, [relationshipid] [smallint] NULL, [relationid] [smallint] NULL, CONSTRAINT [PK_detailtable] PRIMARY KEY CLUSTERED  ([id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    ALTER TABLE [dbo].[detailtable]  WITH CHECK ADD  CONSTRAINT [FK_detailtable_detailtable] FOREIGN KEY([relationid])
    REFERENCES [dbo].[detailtable] ([id])

    ALTER TABLE [dbo].[detailtable] CHECK CONSTRAINT [FK_detailtable_detailtable]

    ALTER TABLE [dbo].[detailtable]  WITH CHECK ADD  CONSTRAINT [FK_detailtable_relationshipmaster] FOREIGN KEY([relationshipid])
    REFERENCES [dbo].[relationshipmaster] ([relationid])

    ALTER TABLE [dbo].[detailtable] CHECK CONSTRAINT [FK_detailtable_relationshipmaster]

    INSERT

    INTO RELATIONSHIPMASTER VALUES(1, 'PARENT')


    INSERT

    INTO RELATIONSHIPMASTER VALUES(2, 'SPOUSE')


    INSERT

    INTO RELATIONSHIPMASTER VALUES(3, 'CHILD')


    INSERT

    INTO RELATIONSHIPMASTER VALUES(4, 'SISTER')

    Sunday, November 25, 2012 11:31 AM
  • To start with, there are a few pecularlities in your tables. All columns
    beside the id columns are NULL. What does NULL in
    relationshipmaster.relationname mean? That here is a some kind of relation,
    but we don't know what it is?

    In the same vein detailtable.name is NULL. Is this to handle persons of
    which we don't know the name? We know that Paul got a son when he was very
    young, but the name of the son is forgotten?

    The table names are also confusing. relationshipmaster does not seem like a
    master table to. Rather it is a lookup table that defines different relation
    types, and there is not much about details in the detailtable.

    Anyway, for your actual problem, you need a trigger that runs a recursive
    CTE with cycle detectetion. The easiest way, I think, is to build a path and
    if an id you arrive to already exists in the path, you have a cycle. Below
    is an example. The table variable that appears is not needed as such, but
    limitations in T-SQL syntax prevents us from using a CTE in an IF statement.

    CREATE TRIGGER cycle_tri ON detailtable AFTER INSERT, UPDATE AS

    DECLARE @cycles TABLE (cycle varchar(MAX) NOT NULL)

    ; WITH rekurs AS (
        SELECT relationid,
               path = convert(varchar(MAX), ' ' + ltrim(str(relationid)) + ' '),
               iscycle = convert(bigint, 0)
        FROM   inserted
        WHERE  relationshipid = 1
        UNION  ALL
        SELECT d.relationid, path + ltrim(str(d.relationid)) + ' ',
               iscycle = charindex(' ' + ltrim(str(d.relationid)) + ' ', path)
        FROM   rekurs r
        JOIN   detailtable d ON r.relationid = d.id
        WHERE  d.relationshipid = 1
          AND  r.iscycle = 0
    )
    INSERT @cycles(cycle)
       SELECT path
       FROM   rekurs
       WHERE  iscycle > 0

    IF EXISTS (SELECT * FROM @cycles)
    BEGIN
       ROLLBACK TRANSACTION
       RAISERROR ('Cycles detected in the data!', 16, 1)
    END
    go
    INSERT detailtable(name, id, relationid, relationshipid)
      VALUES ('Paul',    1, 2, 2),
             ('Richard', 2, 1, 2)
    go
    INSERT detailtable(name, id, relationid, relationshipid)
      VALUES ('Jack', 11, 12, 1),
             ('Jill', 12, 11, 1)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Sunday, November 25, 2012 3:33 PM
    • Marked as answer by Kalman Toth Friday, November 30, 2012 4:10 PM
    Sunday, November 25, 2012 1:35 PM
  • Google the Nested Sets model for hierarchies.  In the future, post DDL and read at least one book on basic data modeling; none of your data elements are correct. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, November 25, 2012 3:08 PM
  • First you need an explicit model of what is relevant/ irrelevant. For example what if (Z SPOUSE OF X) and (X SPOUSE OF Y) and ( Y SPOUSE OF W)?

    If this model is rather complicated consider implementing it at the application level ( stored procedures or something).


    Serg

    Sunday, November 25, 2012 5:08 PM
  • Sorry for the inconvience. The fields relationname in relationshipmaster and name in detailtable are not null.

    Letme try your code.

    thank you

    Sunday, November 25, 2012 7:48 PM