10 things to review after finishing your data model

When I finish modeling my database, I like to just let it sit a couple days, then spend some time just reviewing it to check for inconsistencies. The kind of inconsistencies you never notice when you’re up to your eyeballs in details, but drive you up the wall after 5 years of maintenance.

When I finished my database design a few days ago, I jotted a few things down to remember to do. But when I transcribed them into my bug tracker, where I manage all my tasks, 3 things turned into 5, then 7 and I realized if I could just add a few more, I’d have an infamous ‘Top 10′ list.

Anyway here it is. It’s not exactly comprehensive, but it’s a start.

10 things to review after finishing your data model:

1. Naming consistencies
2. Column default consistencies
3. Identity / auto-number technique exists
4. Constraint consistencies
5. Foreign Key relationships exist
6. Indexes on Foreign Key columns
7. Abbreviations are consistent
8. Abbreviations are documented
9. Data type and size consistencies. (For example; TableA.UserName is nvarchar(N), so TableB.UserName should be nvarchar(N) as well, not nvarchar(N±X))
10. Review all requirements again to reconfirm everything was covered

Thoughts grow to tweets, then blogs, then they just die

I’ll often want to Tweet something, but feel the need to explain further in a second Tweet. But something in that will need explaining, so it occurs to me that I really need a blog post. But then I realize I should probably post this as 2 or more separate posts to isolate ideas and keep them self contained and just link between them.

Then I realize I’ve got work to do, and drop it till I have time …
… which never comes and my thought dies having never lived.

*Even this one tweet expanded into a blog post!

… so I can worry about curly braces

I just wanted to take this Remembrance Day opportunity to thank all the Canadian soldiers, past & present, living & dead, along with those of our allies, for your dedication & sacrifice.

Thank you for fighting for our liberties so we can worry about the more important things like; litterbugs, dynamic vs. static programming languages, and the endless irrelevant debates about curly braces.

Canadian National War Memorial - Tomb of the Unknown Soldier

This image taken by Andrew Moor

How to enforce a foreign key constraint against multiple tables

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 :

1. Ditch the referential integrity, which I am vehemently opposed to. ++
2. Create multiple flag tables, each with the exact same schema, but a different Foreign Key relationship, which just seems wrong.
3. 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.

How to hook up NUnit as an option in the ASP.NET MVC application wizard

Ben Alabaster and I have started a project, lets call it Project X for now (original eh? .

Anyway, we have decided to blog about its development. I will be posting something in the next few days, and Ben just published his first post “How do I hook my version of nUnit into the ASP.NET MVC template?“. Here’s a blurb:

If you’ve been looking for a way to integrate nUnit into your ASP.NET MVC 1.0 template – that is, when you create a new ASP.NET MVC application and it asks you if you’d like to create a test project, nUnit shows up in the list along with the usual Visual Studio Unit Test option.

There are a number of longwinded ways of doing things. There’s also a relatively simple way touted on the Visual Web Developer Team Blog which I’ll spare you the headache of running it and finding the same problems I did….

Ben’s post can be found on his blog endswithsaurus.com