Enforcing Referential Integrity with Triggers Instead of Foreign Keys

I'm building a new database for a web-based application and find that I am frequently having to decide between flexibility of the model and meaningful foreign keys to enforce referential integrity.

There are a couple of aspects of the design that lead me towards writing triggers to do what FKs would normally do:

1. Parts of the model use the Class Table Inheritance Pattern and some of the data tables have an ObjectID whose underlying type should be restricted to a subset of object types. This is pretty easy to do in a trigger, but impossible in a FK without further complicating the data model.

2. The database has a very flexible reference data model that allows end users to customize their instance of the database (each client will have their own database) with new fields as well as extending the list of predefined values for common fields. At first, I had a hundred little tables with exactly the same schema (ID, Name) but have since consolidated them all into a single table (FieldID, ID, Name). Again, this would be pretty straightforward to check in a trigger, but impossible in a FK

Some other details:

• As mentioned above, each client will have their own copy of the database
• The size of each database is not likely to very big. Probably somewhere in the 10 - 50 GB range
• MS SQL 2008

Does this idea sound reasonable? Or are there some huge pitfalls that I'm not thinking about? The reason I would be creating foreign keys is to enforce data integrity and prevent orphaned rows. As long as that end is accomplished the means shouldn't matter, right?

EDIT: I feel like I should clarify that I am not intending to perform ALL referential integrity checks with triggers. When I can, I wil开发者_JAVA百科l use a foreign key. There are a just a couple of areas in my model where I can't. I appreciate the thoughtful answers so far.

From your description, it seems to me the triggers will get more and more complex over time, and will end up being a nightmare to maintain.

I have had to maintain this kind of "ObjectId" data schema in my career, and my experience with it has always been negative. The maintenance becomes very painful over time, and it becomes very complicated to perform meaningful queries. Essentially what you would be doing would be abandoning a "real" relational model for a sort of metadata model.

It may seem counterintuitive, but maintaining a properly normalized relational model, even one with many tables, is (generally) easier than maintaining a metadata model.

All that said, if I were going to go the "ObjectId" route, I would consider enforcing integrity in my application layer and not using triggers. The downside would be that it would make it possible to get bad data in the system (logical bugs or people typing in data manually through SSMS). However the maintenance would likely be more manageable.

Without any idea of your application's logic or table structure I can't comment further than saying that it has been my experience that the complexity of your queries will increase as the flexibility of the data model increases. With this comes performance pain as well.

Also, in regards to foreign keys, I found this...

reasons for defining foreign key constraints

• They physically define the business by preventing data integrity issues in your database. (e.g. The database prevents line items from being created without an existing order header)
• They logically document the business by showing how all data relates to each other. To someone new to your organization, this allows him/her to get a good understanding of how the business works. (e.g. Every order taken must have a valid customer assigned)
• Foreign Keys are native to SQL Server and are designed to prevent data integrity issues. Business logic developers should not be in the business of verifying table relationships.
• If defined and indexed correctly, they can be leveraged by the SQL Server query engine to generate extremely efficient query plans.

from http://www.mssqltips.com/tip.asp?tip=1296

Using triggers to implement a more complex referential integrity rule is ok, but can be confusing to new developers so make sure it's well documented internally.

Having clients customize their database structure is asking for trouble though. It's very likely to cause maintenance problems down the road. A better option is to create a universal structure that can hold any data, such as a table of key/value pairs.

You are using a relational database system to store a set of key-value pairs. That means that you are not using the full power of a relational system. If you really think that key-value pairs are the best way of storing your data, then you should consider using something other than an RDBMS. Clearly, the database technology is not matched to your data storage needs.

You should look into NoSQL and structured data storage.