I am building a web app with Ben Alabaster, and one of the requirements is for the user to be able to flag items for moderators. So the user can flag entity A, entity B, entity C, etc…
So I created a single flag table.
Flag Table
Which I then tried to tie it to the entity tables, hoping for something like
Ideal Foriegn Key Relationships
Where all the foreign key relationships were from [flag].[entity_id] to [EntityX].[id]
Then when I wanted the top 10 flags from a particular entity (B in this case), I could run a query like
select top 20 e.[name], count(*) "count"
from entityB as e
left join flag as f
on f.entity_id = e.id
where f.entity_type='B'
group by e.[name]
order by count(*) desc
Unfortunately, if you were to create the above table relationship, and run the following inserts
insert into EntityA( id, name) values (1, 'EntityA');
insert into EntityB( id, name) values (2, 'EntityB');
insert into EntityC( id, name) values (3, 'EntityC');
insert into EntityD( id, name) values (4, 'EntityD');
The following statement
insert into flag(entity_id, flag_reason) values(5, 'Testing without a valid FK value.');
would fail as expected, as expected, with the following error. “The INSERT statement conflicted with the FOREIGN KEY constraint “FK_flag_EntityA”. The conflict occurred in database “test”, table “dbo.EntityA”, column ‘id’.”
But
insert into flag(entity_id, flag_reason) values(1, 'Testing the FK to entity A.');
would also fail, which was undesired, with the following error: “The INSERT statement conflicted with the FOREIGN KEY constraint “FK_flag_EntityB”. The conflict occurred in database “test”, table “dbo.EntityB”, column ‘id’.” +
So, my options with regards to referential integrity are :
- Ditch the referential integrity, which I am vehemently opposed to. ++
- Create multiple flag tables, each with the exact same schema, but a different Foreign Key relationship, which just seems wrong.
- Managing referential integrity via triggers.
While I’m not a big fan of triggers, the ‘Managing referential integrity via triggers.’ option seems like the only tolerable one. So I added the [entity_type] column to my flag table.
Flag Table With Entity Type
Removed the relationships
No Relationships
And wrote the following trigger to manage the foreign key relationship.
-- =============================================
-- Description: maintain referential integrity on
-- a column which is a FK for different tables
-- =============================================
CREATE TRIGGER flag_entity_id_fk
ON flag
AFTER INSERT,UPDATE
AS
BEGIN
declare @entity_type char(1);
declare @entity_id int;
declare @cnt int;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- get info
select @entity_type=entity_type,
@entity_id=entity_id,
@cnt=0
from inserted;
-- check if records exist
if 'A' = @entity_type
begin
select @cnt=count(*)
from entityA
where id=@entity_id;
end
else if 'B' = @entity_type
begin
select @cnt=count(*)
from entityB
where id=@entity_id;
end
else if 'C' = @entity_type
begin
select @cnt=count(*)
from entityC
where id=@entity_id;
end
else if 'D' = @entity_type
begin
select @cnt=count(*)
from entityD
where id=@entity_id;
end
-- records exist? exit
if 0 < @cnt
begin
return;
end
-- no? error
raiserror( 'Unable to find foriegn key match on entity type ''%s'', id ''%d''.', 16, 1, @entity_type, @entity_id);
rollback transaction;
END
Now, when you run
insert into flag(entity_type, entity_id, flag_reason) values('B', 5, 'Testing without a valid FK value.');
The trigger doesn’t find a match in the appropriate table, rolls back the insert, and gives you a descriptive error message.
Unable to find foriegn key match on entity type ‘B’, id ‘5′.
However, a good value is accepted.
insert into flag(entity_type, entity_id, flag_reason) values('B', 2, 'Testing without a valid FK value.');
I’m still not happy with this approach, but it does seem to be the lesser of all the evils. Please let me know with a comment if there is another option I’ve overlooked. Thanks.
* Frankly I was surprised it even compiled.
+ Unless of course you were unfortunate enough to test this in a coincidental situation where all tables happened to contain the id of every test you ran.
++ Yes ‘vehemently’
EDIT (11/10/2009) : It just occurred to me that this article does not take into account what would happen if the entity tables were to delete a row which this table was pointing to. When I designed my tables this was taken into account, but since we are not planning to allow actual deletions, it was left out. However, if you were to implement this strategy, where entities could be deleted, a delete trigger would need to be created for each entity table.
Posted by John MacIntyre 
. 
