多种类型实体的数据库设计

I need to develop an application where there will be 4 types of user entities (administrators, partners, companies and clients), each user type has it's own set of details and they all should be able to do common operations like send messages, make payments and so on. These operations should be kept on a single table but they need to reference the exact user despite it's type.

我需要开发一个应用程序，其中将有4种类型的用户实体（管理员，合作伙伴，公司和客户），每个用户类型都有自己的一组详细信息，他们都应该能够执行常见的操作，如发送消息，付款等等。这些操作应保存在单个表中，但它们需要引用确切的用户，尽管它是类型。

What database design would be more appropriate?

什么数据库设计更合适？

4 个解决方案

#1

"I would just like to add one more thing, you suggest I have a table per each user type... I prefer this approach however how would I design a schema where I can say that user id 7 (admin) sent a message to user id 537 (client)? Or that a payment was received by user id 70 (company)?"

“我只想再添加一个东西，你建议每个用户类型都有一个表格...我更喜欢这种方法但是我如何设计一个架构，我可以说用户ID 7（管理员）发送了一条消息给用户ID 537（客户端）？或用户ID 70（公司）收到付款？“

There is nothing to stop you from doing that. Have a table {sender recipient message(-id)} with primary key all three attributes and two FK {sender} and {recipient}. The FK's refer to the primary key of the table that holds the COMMON attributes of all users.

没有什么可以阻止你这样做。有一个表{sender recipient message（-id）}，主键有三个属性，两个FK {sender}和{recipient}。 FK指的是包含所有用户的COMMON属性的表的主键。

Now, your next question may be, "but I want a rule to say that no user of type X can directly send a message to any user of type Y".

现在，您的下一个问题可能是，“但我想要一条规则，即X类型的用户不能直接向Y类型的任何用户发送消息”。

That is the point where any current IMPLEMENTATION of a (so-called) relational DBMS shows its weaknesses. Even Oracle or DB2 can't do that declaratively. There is simply too very much for me to say about that subject to fit in this response.

这就是所谓的（所谓的）关系DBMS的当前实现显示其弱点的点。甚至Oracle或DB2也无法以声明方式执行此操作。对于我说这个主题非常适合这个回答。

BTW You seemed to have taken an interest in my response despite all the downvotes. Really appreciate that.

尽管如此，你似乎对我的回答感兴趣了。真的很感激。

#2

I'd say this is a perfect case for inheritance. Put the common attributes in one table and inherit that to add custom attribute for your different user types.

我想说这是继承的完美案例。将公共属性放在一个表中并继承该属性以为不同的用户类型添加自定义属性。

Chaos answer seems a bit messy to me, alltough it'd be useful if you don't know in advance what the properties you need to store are.

混乱的答案对我来说似乎有些混乱，如果你事先不知道你需要存储的属性是什么，那么它会很有用。

#3

Have a look at the three ways to do that in the Patterns of Enterprise Application Architecture:

在“企业应用程序架构模式”中查看三种方法：

http://martinfowler.com/eaaCatalog/singleTableInheritance.html

http://martinfowler.com/eaaCatalog/classTableInheritance.html

http://martinfowler.com/eaaCatalog/concreteTableInheritance.html

The choice depends on how many properties the 4 types of user entities will be sharing and also on the use cases that your system will require.

选择取决于4种类型的用户实体将共享多少属性，以及系统将需要的用例。

#4

-2

user
================
id
user_type_id
name
etc

user_type
================
id
name (admin, partner...)
etc

user_detail
================
id
user_id
user_detail_type_id
value

user_detail_type
================
id
name

user_type_to_user_detail_type
================
id
user_type_id
user_detail_type_id
(maps which user types have which detail types)

#1