SQLServer | While I Compile

Nov 29, 2009 Posted by John MacIntyre

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: Naming consistencies Column default consistencies Identity / auto-number technique exists Constraint consistencies Foreign Key relationships exist Indexes on Foreign Key columns Abbreviations are consistent Abbreviations are documented 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)) Review all requirements again to reconfirm everything was... read more

Nov 9, 2009 Posted by John MacIntyre

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. Which I then tried to tie it to the entity tables, hoping for something like 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. Removed the 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,... read more

May 1, 2009 Posted by John MacIntyre

6 simple steps to a stress free database change deployment

… Deployments can be a real headache at the best of times, but especially when schema updates to a production database are involved. Don’t get me wrong, you usually have a backup to fall back on, but how long will that take to restore? … Really, you don’t want to resort to the restore, have the database offline for that long, or have your name associated to it. So gradually I evolved a process which has kept me sane and confident when deploying schema changes to production servers, even on large, sensitive, and active databases….

How To Get The Most Frequently Used Column Values

Whenever I import external data, integrate to another database, or am new to a project, I need to get familiar with the database. The table schemas, relational integrity, and constraints are the first thing I look at and take me a long way, but soon I need to know what the data looks like.

In an ideal world, relational integrity and database constraints would define control this, and all I’d really need to do is look at those. But the reality is, in 15 years of working in this industry, most of the databases I’ve worked on, that I didn’t design, have barely used constraints and some haven’t even used relation integrity fully!

The need to get a good feel of the data is even more prevalent when working with dirty data, or when refactoring poorly written applications to ensure any refactoring doesn’t introduce other issues. I will usually wind up writing the following query repeatedly:
….