我正在为基于 Web 的应用程序构建一个新数据库，并发现我经常必须在模型的灵活性和有意义的外键之间做出决定以强制引用完整性。
设计的几个方面促使我编写触发器来执行 FK 通常会执行的操作：
-
模型的部分内容使用 类表继承模式并且某些数据表具有ObjectID，其基础类型应限制为对象类型的子集。这在触发器中很容易做到，但在 FK 中则不可能，除非数据模型进一步复杂化。
-
该数据库具有非常灵活的参考数据模型，允许最终用户使用新字段自定义其数据库实例（每个客户端将拥有自己的数据库）以及扩展公共字段的预定义值列表。起初，我有一百个具有完全相同架构（ID、名称）的小表，但后来将它们全部合并到一个表中（FieldID、ID、名称）。同样，这对于检查触发器来说非常简单，但在 FK 中是不可能的。
其他细节：
- 如上所述，每个客户端都有自己的数据库副本
- 每个数据库的大小不太可能很大。可能在 10 - 50 GB 范围内
- MS SQL 2008
这个想法听起来合理吗？还是有一些我没有考虑到的巨大陷阱？我创建外键的原因是为了强制数据完整性并防止出现孤立行。只要目的达到了，手段就不重要了，对吧？
编辑：我觉得我应该澄清一下，我不打算使用触发器执行所有引用完整性检查。如果可以的话，我会使用外键。我的模型中只有几个区域我无法做到。我很欣赏迄今为止深思熟虑的答案。
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:
-
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.
-
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 will 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.
发布评论
评论（4）
从你的描述来看，在我看来，随着时间的推移，触发器会变得越来越复杂，并且最终将成为维护的一场噩梦。
在我的职业生涯中，我不得不维护这种“ObjectId”数据模式，而我对它的体验一直都是负面的。随着时间的推移，维护变得非常痛苦，并且执行有意义的查询变得非常复杂。本质上，您要做的就是放弃“真正的”关系模型，转而采用某种元数据模型。
这可能看起来违反直觉，但维护一个适当规范化的关系模型，即使是具有许多表的模型，（通常）比维护元数据模型更容易。
话虽如此，如果我要走“ObjectId”路线，我会考虑在应用程序层中强制执行完整性，而不是使用触发器。缺点是可能会在系统中获取错误数据（逻辑错误或人们通过 SSMS 手动输入数据）。然而，维护可能会更容易管理。
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...
使用触发器来实现更复杂的引用完整性规则是可以的，但可能会让新开发人员感到困惑，因此请确保其在内部有详细记录。
不过，让客户定制他们的数据库结构是自找麻烦。这很可能会导致日后的维护问题。更好的选择是创建一个可以保存任何数据的通用结构，例如键/值对表。
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.
您正在使用关系数据库系统来存储一组键值对。这意味着您没有充分利用关系系统的全部功能。如果您确实认为键值对是存储数据的最佳方式，那么您应该考虑使用 RDBMS 以外的其他方式。显然，数据库技术与您的数据存储需求不匹配。
您应该研究 NoSQL 和结构化数据存储。
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.