SQL “政党模式”背后的原则和好处是什么？

What are the principles behind, and benefits of, the "party model"?

提问by Charlie Flowers

The "party model" is a "pattern" for relational database design. At least part of it involves finding commonality between many entities, such as Customer, Employee, Partner, etc., and factoring that into some more "abstract" database tables.

“派对模式”是关系数据库设计的一种“模式”。它至少有一部分涉及找到许多实体（例如客户、员工、合作伙伴等）之间的共性，并将其分解为一些更“抽象”的数据库表。

I'd like to find out your thoughts on the following:

我想了解您对以下问题的看法：

1. What are the core principles and motivating forces behind the party model?
2. What does it prescribe you do to your data model? (My bit above is pretty high level and quite possibly incorrect in some ways. I've been on a project that used it, but I was working with a separate team focused on other issues).
3. What has your experience led you to feel about it? Did you use it, and if so, would you do so again? What were the pros and cons?
4. Did the party model limit your choice of ORMs? For example, did you have to eliminate certain ORMs because they didn't allow for enough of an "abstraction layer" between your domain objects and your physical data model?

1. 党的模式背后的核心原则和动力是什么？
2. 它对你的数据模型做了什么规定？（我上面的内容相当高级，在某些方面很可能不正确。我一直在使用它的项目，但我正在与一个单独的团队合作，专注于其他问题）。
3. 你的经历让你有什么感受？您是否使用过它，如果使用过，您会再次使用它吗？利弊是什么？
4. 派对模式是否限制了您对 ORM 的选择？例如，您是否必须消除某些 ORM，因为它们不允许在域对象和物理数据模型之间有足够的“抽象层”？

I'm sure every response won't address every one of those questions ... but anything touching on one or more of them is going to help me make some decisions I'm facing.

我敢肯定，每个回复都不会解决所有这些问题……但任何涉及其中一个或多个问题的内容都将帮助我做出我面临的一些决定。

Thanks.

谢谢。

采纳答案by Jeremy Stanley

1. What are the core principles and motivating forces behind the party model?

1. 党的模式背后的核心原则和动力是什么？

To the extent that I've used it, it's mostly about code reuse and flexibility. We've used it before in the guest / user / admin model and it certainly proves its value when you need to move a user from one group to another. Extend this to having organizations and companies represented with users under them, and it's really providing a form of abstraction that isn't particularly inherent in SQL.

就我使用它而言，它主要是关于代码重用和灵活性。我们之前在访客/用户/管理员模型中使用过它，当您需要将用户从一个组移动到另一个组时，它肯定证明了它的价值。将其扩展为让组织和公司以用户为代表，它确实提供了一种在 SQL 中并不是特别固有的抽象形式。

2. What does it prescribe you do to your data model? (My bit above is pretty high level and quite possibly incorrect in some ways. I've been on a project that used it, but I was working with a separate team focused on other issues).

2. 它对你的数据模型做了什么规定？（我上面的内容相当高级，在某些方面很可能不正确。我一直在使用它的项目，但我正在与一个单独的团队合作，专注于其他问题）。

You're pretty correct in your bit above, though it needs some more detail. You can imagine a situation where an entity in the database (call it a Party) contracts out to another Party, which may in turn subcontract work out. A party might be an Employee, a Contractor, or a Company, all subclasses of Party. From my understanding, you would have a Party table and then more specific tables for each subclass, which could then be further subclassed (Party -> Person -> Contractor).

您在上面的部分非常正确，尽管它需要更多细节。您可以想象这样一种情况，数据库中的一个实体（称其为一方）将合同外包给另一方，而另一方可能会转包。一方可能是员工、承包商或公司，所有子类都是一方。根据我的理解，您将有一个 Party 表，然后是每个子类的更具体的表，然后可以进一步子类化（Party -> Person -> Contractor）。

3. What has your experience led you to feel about it? Did you use it, and if so, would you do so again? What were the pros and cons?

3. 你的经历让你有什么感受？您是否使用过它，如果使用过，您会再次使用它吗？利弊是什么？

It has its benefits if you need flexibly to add new types to your system and create relationships between types that you didn't expect at the beginning and architect in (users moving to a new level, companies hiring other companies, etc). It also gives you the benefit of running a single query and retrieving data for multiple types of parties (Companies,Employees,Contractors). On the flip side, you're adding additional layers of abstraction to get to the data you actually need and are increasing load (or at least the number of joins) on the database when you're querying for a specific type. If your abstraction goes too far, you'll likely need to run multiple queries to retrieve the data as the complexity would start to become detrimental to readability and database load.

如果您需要灵活地向系统添加新类型，并在开始时没想到的类型和架构师之间创建关系（用户升级到新级别，公司雇用其他公司等），那么它有其好处。它还为您提供了运行单个查询并为多种类型的各方（公司、员工、承包商）检索数据的好处。另一方面，您正在添加额外的抽象层以获取您实际需要的数据，并且在查询特定类型时增加了数据库的负载（或至少是连接数）。如果您的抽象走得太远，您可能需要运行多个查询来检索数据，因为复杂性将开始损害可读性和数据库负载。

4. Did the party model limit your choice of ORMs? For example, did you have to eliminate certain ORMs because they didn't allow for enough of an "abstraction layer" between your domain objects and your physical data model?

4. 派对模式是否限制了您对 ORM 的选择？例如，您是否必须消除某些 ORM，因为它们不允许在域对象和物理数据模型之间有足够的“抽象层”？

This is an area that I'm admittedly a bit weak in, but I've found that using views and mirrored abstraction in the application layer haven't made this too much of a problem. The real problem for me has always been a "where is piece of data X living" when I want to read the data source directly (it's not always intuitive for new developers on the system either).

诚然，我在这方面有点薄弱，但我发现在应用程序层中使用视图和镜像抽象并没有造成太大问题。当我想直接读取数据源时，对我来说真正的问题一直是“数据 X 生活在哪里”（对于系统上的新开发人员来说，这也并不总是直观）。

回答by Michel Triana

The idea behind the party models (aka entity schema) is to define a database that leverages some of the scalability benefits of schema-free databases. The party model does that by defining its entities as party type records, as opposed to one table per entity. The result is an extremely normalized database with very few tables and very little knowledge about the semantic meaning of the data it stores. All that knowledge is pushed to the data access in code. Database upgrades using the party model are minimal to none, since the schema never changes. It's essentially a glorified key-value pair data model structure with some fancy names and a couple of extra attributes.

参与方模型（又名实体模式）背后的想法是定义一个数据库，该数据库利用无模式数据库的一些可扩展性优势。参与方模型通过将其实体定义为参与方类型记录来实现这一点，而不是每个实体一个表。结果是一个极其规范化的数据库，它的表很少，而且对其存储的数据的语义知之甚少。所有这些知识都被推送到代码中的数据访问中。使用派对模型的数据库升级很少甚至没有，因为架构永远不会改变。它本质上是一个美化的键值对数据模型结构，带有一些花哨的名称和一些额外的属性。

Pros:

优点：

• Kick-ass horizontal scalability. Once your 5-6 tables are defined in your entity model, you can go to the beach and sip margaritas. You can virtually scale this database out as much as you want with minimum efforts.
• The database supports any data structure you throw at it. You can also change data structures and party/entities definitions on the fly without affecting your application. This is very very powerful.
• You can model any arbitrary data entity by adding records, not changing the schema. Meaning you can say goodbye to schema migration scripts.
• This is programmers' paradise, since the code they write will define the actual entities they use in code, and there are no mappings from Objects to Tables or anything like that. You can think of the Party table as the base object of your framework of choice (System.Object for .NET)

• 踢屁股水平可扩展性。一旦在实体模型中定义了 5-6 个表，您就可以去海滩啜饮玛格丽塔酒。您几乎可以以最少的努力尽可能多地扩展此数据库。
• 数据库支持您投入的任何数据结构。您还可以在不影响您的应用程序的情况下动态更改数据结构和方/实体定义。这是非常非常强大的。
• 您可以通过添加记录而不是更改架构来对任意数据实体进行建模。这意味着您可以告别模式迁移脚本。
• 这是程序员的天堂，因为他们编写的代码将定义他们在代码中使用的实际实体，并且没有从对象到表或类似的映射。您可以将 Party 表视为您选择的框架的基本对象（System.Object for .NET）

Cons:

缺点：

• Party/Entity models never play well with ORMs, so forget about using EF or NHibernate to get semantically meaningful entities out of your entity database.
• Lots of joins. Performance tuning challenges. This ‘con' is relative to the practices you use to define your entities, but is safe to say that you'll be doing a lot more of those mind-bending queries that will bring you nightmares at night.
• Harder to consume. Developers and DB pros unfamiliar with your business will have a harder time to get used to the entities exposed by these models. Since everything is abstract, there no diagram or visualization you can build on top of your database to explain what is stored to someone else.
• Heavy data access models or business rules engines will be needed. Basically you have to do the work of understanding what the heck you want out of your database at some point, and your database model is not going to help you this time around.

• 派对/实体模型永远不会与 ORM 配合得很好，所以忘记使用 EF 或 NHibernate 从实体数据库中获取语义上有意义的实体。
• 很多加盟。性能调优挑战。这种“骗局”与您用来定义实体的做法有关，但可以肯定地说，您将执行更多令人费解的查询，这些查询会给您带来夜间噩梦。
• 更难消费。不熟悉您的业务的开发人员和数据库专业人员将更难习惯这些模型公开的实体。由于一切都是抽象的，因此您无法在数据库之上构建图表或可视化来向其他人解释存储的内容。
• 将需要大量数据访问模型或业务规则引擎。基本上，您必须在某个时候了解您想要从数据库中获得什么，而您的数据库模型这次不会帮助您。

If you are considering a party or entity schema in a relational database, you should probably take a look at other solutions like a NoSql data store, BigTable or KV Stores. There are some great products out there with massive deployments and traction such as MongoDB, DynamoDB, and Cassandra that pioneered this movement.

如果您正在考虑关系数据库中的一方或实体模式，您可能应该看看其他解决方案，如 NoSql 数据存储、BigTable 或 KV 存储。有一些伟大的产品具有大规模部署和牵引力，例如 MongoDB、DynamoDB 和 Cassandra，它们开创了这一运动。

回答by Michel Triana

When I was part of a team implementing these ideas in the early 1980's, it did not limit our choice of ORM's because those hadn't been invented yet.

在 1980 年代初期，当我成为实现这些想法的团队的一员时，它并没有限制我们对 ORM 的选择，因为它们还没有被发明。

I'd fall back on those ideas any time, as that particular project was one of the most convincing proofs-of-concept I have ever seen of a "revolutionary" idea (which it certainly was at the time).

我随时都会重新考虑这些想法，因为那个特定项目是我见过的“革命性”想法（当时确实如此）最令人信服的概念证明之一。

It forces you to nothing. And it doesn't stop you from anything (from any mistake, I mean). The one defining your own information model is you.

它迫使你一事无成。它不会阻止你做任何事情（任何错误，我的意思是）。定义您自己的信息模型的是您。

All parties have lots of properties in common. The fact that they have a name and such (we called those "signaletics"). The fact that they have principal/primary locations called "addresses". The fact that they all are involved, in some sense, in the business' contracts.

各方都有很多共同点。事实上，他们有一个名字等等（我们称之为“信号学”）。他们拥有称为“地址”的主要/主要位置的事实。事实上，在某种意义上，他们都参与了企业的合同。

回答by kitsune

This is a vast topic, I would recommend reading The Data Model Resource Book Volume 3 - Universal Patterns for Data Modelingby Len Silverston and Paul Agnew.

这是一个很大的话题，我建议您阅读Len Silverston 和 Paul Agnew 所著的数据模型资源书第 3 卷 - 数据建模的通用模式。

I've just received my copy and it's pretty good - It provides you with an overlook for many approaches to data modeling, including hybrid contextual role patterns and so on. It has detailed PROs and CONs for every approach.

我刚刚收到我的副本，它非常好 - 它为您提供了许多数据建模方法的概览，包括混合上下文角色模式等。它对每种方法都有详细的优点和缺点。

There is a pletheora of ways to model party relationships and roles all with their benefits and disadvantages. The question that was accepted as an answer covers just one instance of a 'party model'.

有很多方法可以模拟政党关系和角色，以及它们的优点和缺点。被接受作为答案的问题仅涵盖“派对模式”的一个实例。

For instance, in many approaches, notions like "Employee", "Project Manager" etc. are rolesthat a party can play within a certain context. I will try to give you a better breakdown once I get home.

例如，在许多方法中，“员工”、“项目经理”等概念是一方可以在特定上下文中扮演的角色。一旦我回家，我会尽量给你一个更好的细分。

回答by Rzassar

as a simple talk from my understanding: Party modeling gives the flexibility and needs more effort (like T-sql join and ...) to be implemented.
I also wanna point that, "using Party modeling (serialization/generalization) gives you the ability to have FK-Relationto other tables". for example: think of different types of users (admin, user, ...) which generalized into Usertable, and you can have UserIDin your Authorizationtable.

根据我的理解，作为一个简单的谈话：派对建模提供了灵活性，需要更多的努力（如 T-sql join 和 ...）来实现。
我还想指出，“使用 Party 建模（序列化/泛化）使您能够将FK-Relation与其他表建立联系”。例如：考虑归纳为User表的不同类型的用户（管理员、用户、...），您可以UserID在Authorization表中拥有。

回答by Walter Mitty

I'm not sure, but the party model sounds like a particular case of the generalization-specialization pattern. A search on "generalization specialization relational modeling" finds some interesting articles.

我不确定，但派对模型听起来像是泛化-专业化模式的一个特例。搜索“泛化专业化关系建模”会发现一些有趣的文章。