Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Denver
    Posts
    6

    Red face Unanswered: conditional relationship to multiple tables

    Hello all.

    I have what I think is an interesting database issue. In a nutshell, I want to know if it is possible and if so how one can setup a table to optionally relate to different tables. Let me explain, consider the following two tables (in simple psuedo-sql syntax):

    Table Messages
    ------------------
    ID - Int, PK
    Name - varchar
    Type - varchar

    Table MessageFields
    -------------------
    ID - Int, PK
    PID - Int, FK
    Name - varchar
    Type - varchar
    Size - int

    Relationship:
    MessageFields.PID relates to Messages.ID

    These tables store information used to parse messages. They are related via a straight forward one-to-many relationship where the PID in MessageFields is the FK that relates to ID in Messages. In this simple kind of relationship, it is easy to setup referential integrity and cascaded deletes, etc...

    Now, this worked fine as long as each message simply had it's fields and that was it. However, some fields can have sub-fields (if field is an array, it will have x number of subfields corresponding to each array element). Also, those sub-fields can have sub-fields. In fact, there is no set limit, although in practice it will probably only go 3 levels deep in subfields.

    Anyway, the way to represent an arbirary subfield structure like this is to use a recursive table structure, where the FK field in the table (PID in this case) refers to the PK field in the same table (ID), like so:

    Table MessageFields
    -------------------
    ID - Int, PK
    PID - Int, FK
    Name - varchar
    Type - varchar
    Size - int

    Relationship:
    MessageFields.PID relates to MessageFields.ID

    I believe you can even setup referential integrity and cascaded deletes on such a self-referecing, recursive setup.

    The problem is, we still need to relate the MessageFields table to the Messages table. Sooo, the only way to do this that I have come up with is a setup like this:

    Table Messages
    ------------------
    ID - Int, PK
    Name - varchar
    Type - varchar

    Table MessageFields
    -------------------
    ID - Int, PK
    PID - Int, FK
    ParType - char(1)
    Name - varchar
    Type - varchar
    Size - int

    Relationship:
    If ParType = 'M' then
    MessageFields.PID relates to Messages.ID
    elseif ParType = 'F' then
    MessageFields.PID relates to MessageFields.ID
    endif

    Problem is, I don't think it is possible to setup a relationship (and referential integrity) on a condition like this.

    So, my question is, is there a way to setup such a relationship? Is this even a good idea, or is there some standard, better way to setup these tables? Of course, I know I can just setup the tables this way and NOT use a defined relationship, and just be careful in the code that I'm not inserting something incorrect, but I'd rather not. One idea I did have was use a trigger to enforce my referential integrity. The trigger could check inserts into the messagefields table and test the value of partype, then test to see if the inserted row matches the appropriate column in the appropriate table. But before I go down that road, I'd like to see what someone else thinks.

    Thanks much for any info/insight someone can give me on this.

  2. #2
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51
    It's an interesting problem and one I've recently encountered. In our logical model we used subtypes. We have a 'type' of locator with 'subtypes' of physical, tele and postal. So we need to relate entities to locators (many-to-many) based on the type of locator.

    entity:
    entity_id

    entity_locator_participation
    entity_id
    locator_id
    locator_type

    tele_locator
    locator_id
    phone_nbr
    email_addr
    etc...

    postal_locator
    locator_id
    addr1
    addr2
    city
    etc...

    We need to be able to relate entity_locator_participation to tele_locator, postal_locator, or physical_locator depending on the value of the locator_type. For now, we just have no relationship and are maintaining it through code(stored procedures), but it's ugly. We've also thought about encapsulating the logic in triggers.

    It's very similar to the probem you bring up. To the best of my knowledge, there is no way to do this, so I'm interested to see what other solutions people have come up with!

    -Loach

  3. #3
    Join Date
    Nov 2003
    Location
    Denver
    Posts
    6
    Yes, I don't think it is acutally going to be possible to define the conditional relationship directly in sqlserver. I'm leaning towards using a trigger, so at least you can still control the relationship at a db level, and the front end programmers don't have to perform the check. I tested the following trigger, which seems to work for check referential integrity on inserts:

    CREATE TRIGGER [trigger1] ON [dbo].[MessageFields]
    FOR INSERT
    AS
    begin
    declare @id int
    declare @partype varchar
    declare @pid int
    declare @result int

    set @id = (select id from inserted)
    set @pid = (select pid from inserted)
    set @partype = (select partype from inserted)

    if @partype = 'm' or @partype = 'M'
    begin
    set @result = (select count(*) from message where id = @pid)
    if @result = 0
    begin
    print 'problem - no related row in message!'
    delete from messagefields where id = @id
    end
    end
    else if @partype = 'f'
    begin
    set @result = (select count(*) from messagefields where id = @pid)
    if @result = 0
    begin
    print 'problem - no related row in messagefields!'
    delete from messagefields where id = @id
    end
    end

    end


    Now I guess I need to setup the triggers for the update and especially the delete. The delete trigger will be a nested/recursive trigger. This shouldn't be a problem, as sql server allows like 32 levels of trigger nesting, and we'll never get that deep in our hierarchy.

    Tony

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by foxybanjo
    Yes, I don't think it is acutally going to be possible to define the conditional relationship directly in sqlserver. I'm leaning towards using a trigger, so at least you can still control the relationship at a db level, and the front end programmers don't have to perform the check. I tested the following trigger, which seems to work for check referential integrity on inserts:

    CREATE TRIGGER [trigger1] ON [dbo].[MessageFields]
    FOR INSERT
    AS
    begin
    declare @id int
    declare @partype varchar
    declare @pid int
    declare @result int

    set @id = (select id from inserted)
    set @pid = (select pid from inserted)
    set @partype = (select partype from inserted)

    if @partype = 'm' or @partype = 'M'
    begin
    set @result = (select count(*) from message where id = @pid)
    if @result = 0
    begin
    print 'problem - no related row in message!'
    delete from messagefields where id = @id
    end
    end
    else if @partype = 'f'
    begin
    set @result = (select count(*) from messagefields where id = @pid)
    if @result = 0
    begin
    print 'problem - no related row in messagefields!'
    delete from messagefields where id = @id
    end
    end

    end


    Now I guess I need to setup the triggers for the update and especially the delete. The delete trigger will be a nested/recursive trigger. This shouldn't be a problem, as sql server allows like 32 levels of trigger nesting, and we'll never get that deep in our hierarchy.

    Tony
    I had a similar problem in my database and I used this approach:

    I wanted to create some tables to hold some survey template data. The main table held the name of the template and some other general information. There were a number of other tables representing each type of template. A one-many relationship was created between the template table and each of the survey tables. A template type Id in the template table would identify which survey table was to be used and this was set in a view. The table set up looks like:

    Template Table:
    TemplateID - PK
    TemplateName
    TemplateTypeID - identifies which template table you are using

    Customer Survey Template
    CustomerSurveyTemplateID - PK
    TemplateID - FK (one - many with the template table)
    {other customer survey template columns}

    Void Log Survey Template
    VoidLogSurveyTemplateID - PK
    TemplateID - FK (one - many with the template table)
    {other void log survey template columns)

    A view is used for each survey filtered by the templatetypeId . I found this to be very flexible and versatile and easy to use in the front end (in my case an Access database with a data grid)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •