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