将其他要求合并到遗留数据库设计中

I am struggling with a database design, this is what I have so far.

我正在努力进行数据库设计，这是我到目前为止所做的。

Here are the problems.

这是问题所在。

1. I need to introduce a new user type (conglomeration manager), and they will have visibility of groups of companies (a conglomeration). A conglomeration manager can have multiple companies, and a company can belong to multiple conglomeration managers. It would be advantageous if an independent company could be added, and then at a later date be easily included as part of a conglomeration.

   我需要引入一个新的用户类型（集合经理），他们将了解公司集团（集团）。集团经理可以拥有多家公司，公司可以属于多个集团经理。如果可以添加一个独立的公司，然后在以后很容易将其作为集合的一部分包括在内将是有利的。

   I am finding this difficult to model as all my users so far (manager,driver,recipient) all exist in the users table. This was by design as they all have nearly the same data fields, and I need to have a single login point for all users on my site. If I add a conglomeration manager to the users table, they will have relationships with other tables that my existing user types don't have.

   我发现这很难建模，因为到目前为止我的所有用户（经理，驱动程序，收件人）都存在于users表中。这是设计，因为他们都有几乎相同的数据字段，我需要为我的网站上的所有用户都有一个登录点。如果我将一个集合管理器添加到users表，它们将与我现有用户类型所没有的其他表有关系。

2. I am uneasy about the dependency loop formed through users, ownerships, packages, companies, users. This strikes me as bad form, but I really can't think of a way to avoid it as:

   我对通过用户，所有权，包，公司，用户形成的依赖循环感到不安。这让我感觉不好，但我真的想不出办法避免它：

   managers, drivers and recipients all work for a single company. That company has an associated set of packages, yet I need to have the ability to associate a subset of those packages to a particular recipient (they own packages) and to a particular driver or manager (responsible for delivering those packages).

   经理，司机和收件人都为一家公司工作。该公司有一组相关的软件包，但我需要能够将这些软件包的子集与特定的收件人（他们自己的软件包）以及特定的驱动程序或管理器（负责提供这些软件包）相关联。

3. I am not happy with the "receive_emails" field in users, as it is only relevant to users of type "recipient".

   我对用户中的“receive_emails”字段不满意，因为它仅与“收件人”类型的用户相关。

To add to the the problems, this design is already in use, and data will have to be migrated to any new design.

为了解决这些问题，该设计已经投入使用，数据必须迁移到任何新设计。

The most common operations that take place in the system are the viewing of statuses by recipients, followed by the creation of statuses by managers and drivers.

系统中最常见的操作是收件人查看状态，然后由经理和驱动程序创建状态。

Can my problems be addressed with an elegant new design?

优雅的新设计可以解决我的问题吗？

4 个解决方案

#1

Well here is another try. I still think

那么这是另一种尝试。我依然觉得

• you shouldn't worry too much about that receive_emails field, as explained in my other answer.
• 你不应该太担心receive_emails字段，正如我在其他答案中所解释的那样。
• you don't have to split users into user kinds.
• 您不必将用户分成用户类型。

What you are worried about in 2 is the dependencies. Dependencies are usually not a problem, but you are very strict in your id based database design, thus hiding the dependencies from your dbms. If it just knew, it could help you :-)

您在2中担心的是依赖关系。依赖关系通常不是问题，但您在基于id的数据库设计中非常严格，因此隐藏了dbms的依赖关系。如果它只是知道，它可以帮助你:-)

You could do this:

你可以这样做：

• Stick to your table "users", but remove the company_id.
• 坚持你的表“用户”，但删除company_id。
• You don't have to make any changes to "companies", "packages", "participations" and "statuses".
• 您不必对“公司”，“包裹”，“参与”和“状态”进行任何更改。
• Add a table to link users to companies. Let's call the table "affiliations" for the moment. (I don't know if this would be an appropriate name, my English fails me here.) Like ownerships this is just a link table, so the only fields are user_id and company_id (forming the primary key).
• 添加表以将用户链接到公司。我们暂时将表格称为“附属机构”。 （我不知道这是否是一个合适的名字，我的英语在这里失败了。）像所有权一样，这只是一个链表，所以唯一的字段是user_id和company_id（形成主键）。
• Now add company_id to "ownerships". (I know, it is kind of there implicitly because of your link to "packages", but the dbms doesn't know that.) So add the field and now that your dbms sees the field, you can also add a constraint (foreign key) on package_id plus company_id to "packages" and a constraint on user_id plus company_id to "affilations".
• 现在将company_id添加到“所有权”。 （我知道，由于你链接到“包”，它有点隐含，但dbms不知道。）所以添加字段，现在你的dbms看到了字段，你也可以添加一个约束（外来的）密钥）在package_id上​​加上company_id到“packages”以及对user_id的约束加上company_id到“affilations”的约束。

That's it. You haven't changed much, but now a user can be affiliated to many companies (conglomeration managers so far, but maybe you decide one day to allow recipients to work with multiple companies or let drivers work for more than one of the companies at a time). And there is no risk of wrong entries now in "ownerships" or any doubt aboout ist content and use.

而已。你没有太大变化，但现在一个用户可以加入许多公司（集团经理到目前为止，但也许你决定有一天允许收件人与多家公司合作或让司机为一家以上的公司工作时间）。现在“所有权”中没有错误输入的风险，或者对内容和使用有任何疑问。

If you want to play safe with your receive_emails field, here is a way you might want to go (as I said, it is not really necessary): Have a new table email_recipients with two fields: user_id and user_type. Yes, redundancy again. But doing this, you can have a constraint on user_type only allowing certain types (only "recipient" so far). Again you would have a foreign key not only to user_id, but to user_id plus user_type.

如果你想使用你的receive_emails字段安全地玩，这里有一种你可能想要的方式（正如我所说，这不是必要的）：有一个新表email_recipients有两个字段：user_id和user_type。是的，再次冗余。但是这样做，你可以对user_type有一个约束，只允许某些类型（到目前为止只有“收件人”）。再次，您不仅可以使用user_id，还可以使用user_id加上user_type。

#2

Extend users!

扩展用户！

Like extending a class you can create a new table "Managers" with more columns and a FK to users.

与扩展类一样，您可以为用户创建一个包含更多列和FK的新表“Managers”。

So you can create a relational table between Managers and companies.

因此，您可以在经理和公司之间创建关系表。

If you want a better control over that conglomerate entity, create the Conglomerate table and make a FK to managers, so you create a relational table between Conglomerate and Companies OR if a company cannot be owned by two conglomerates just a FK from company to conglomerate.

如果您想要更好地控制该集团实体，请创建Conglomerate表并向经理人提供FK，这样您就可以在Conglomerate和Companies之间创建一个关系表，或者如果一家公司不能由两家企业集团拥有，只需要从公司到集团的FK。

#3

I need to introduce a new user type

我需要引入一个新的用户类型

In these type of scenario when adding a new type is required, that would result in restructuring of schema, leads me to a defect in design.

Actually managing and driving are roles played by a user that may change over time.
In reality:
A user is-not-a manager (he is a person).
Managing is-a-role-played-by a user.
Think about if the company decides to have help-desk users.

I will add Role and User-Role tables to keep the relation between user and role.

在这种情况下，当需要添加新类型时，这将导致模式的重构，这导致我在设计方面存在缺陷。实际上，管理和驾驶是用户扮演的角色，可能随时间而变化。实际上：用户不是经理（他是一个人）。管理是由用户扮演的角色。想想公司是否决定有帮助台用户。我将添加Role和User-Role表来保持用户和角色之间的关系。

I am not happy with the "receive_emails" field in users.

我对用户中的“receive_emails”字段不满意。

Having receive-email field in User-Role will be an option.

在用户角色中接收电子邮件字段将是一个选项。

I need to have a single login point for all users on my site

我需要为我的网站上的所有用户提供一个登录点

May having user, company and role as selections on log-in page helps (that will have direct impacts on your application design).

可以在登录页面上选择用户，公司和角色（这将对您的应用程序设计产生直接影响）。

I am uneasy about the dependency loop formed through users, ownerships, packages, companies, users.

我对通过用户，所有权，包，公司，用户形成的依赖循环感到不安。

Conceptually we will have recipient-user => ownership => package => company => driver or manager user.
BTW its a relational model.

从概念上讲，我们将拥有recipient-user => ownership => package => company => driver或manager user。 BTW是一个关系模型。

Conglomerations.

团块。

#4

3: Don't worry too much about the receive_emails field. Keep in mind that your database is just a model of the real world and doesn't have to be perfect. Yes, you could make "recipients" a table of its own. Consider what you would gain and what you would lose. As it is, it is no bad design. You may use a trigger to set receive_emails to false in case the user is not a recipient. Or use a view in order to hide the field for apps dealing with drivers or managers. Just as you like. Well, if you really want to get rid of the field, you could have a table "emeil_recipients" holding all user ids who are recipients of e-mails. You see there are many ways to address this, and they all have their own advantages and disadvantages. Your design as it is is fine.

3：不要过于担心receive_emails字段。请记住，您的数据库只是现实世界的模型，并不一定非常完美。是的，你可以让“收件人”成为自己的一张桌子。考虑一下你会获得什么以及你会失去什么。事实上，这不是一个糟糕的设计。如果用户不是收件人，您可以使用触发器将receive_emails设置为false。或者使用视图来隐藏处理驱动程序或管理员的应用程序的字段。就像你喜欢的那样。好吧，如果你真的想摆脱这个领域，你可以有一个表“emeil_recipients”，其中包含所有用户ID，他们都是电子邮件的收件人。你看到有很多方法可以解决这个问题，它们都各有利弊。你的设计很好。

2: As far as I understand it, every package has up to one manager, one driver and one recipient. Is that so? Then why have a table "ownerships" at all? Put three fields in your "packages" table; user_id_driver, user_id_manager, user_id_recipient. So your model is much closer to reality. (you can create a view "ownerships" to replace the table "ownerships" during migration time.)

2：据我所知，每个软件包最多只有一个管理员，一个驱动程序和一个接收者。是这样吗？那为什么要有一张表“所有权”呢？将三个字段放在“包”表中; user_id_driver，user_id_manager，user_id_recipient。所以你的模型更接近现实。 （您可以创建视图“所有权”以在迁移期间替换表“所有权”。）

1: Now to the conglomerations. Easiest would be to introduce two new tables: First you would have a table "company_groups" with an id and maybe a description field. Your table "users" would have a field "company_group_id" which would replace the field "company_id". Thus you link users to company groups rather than to single companies. Your second new table would be "company_group_members" with just two fields, id_company_group and id_company. You would build "groups" consisting only of one single company (for the managers, recipients and drivers) and groups consisting of more companies (conglomerations for the conglomerations managers). So your database doesn't change that much, but offers all you need.

1：现在到了集团。最简单的方法是引入两个新表：首先，您将拥有一个带有id的表“company_groups”，可能还有一个描述字段。您的表“users”将有一个字段“company_group_id”，它将替换字段“company_id”。因此，您将用户链接到公司组而不是单个公司。您的第二个新表将是“company_group_members”，只有两个字段，id_company_group和id_company。您将构建仅由一个公司（针对经理，收件人和司机）组成的“组”，以及由更多公司组成的组（集团经理的集团）。所以你的数据库不会改变那么多，但提供你所需要的一切。

Having said all that, you could still think about reducing your table "users" to the common fields and have new tables "managers", "recipients", "drivers" and "conglomeration_managers" holding additional fields. This gets you closer to reality and makes the link to packages clearer. However, it comes at the cost of a more different model from your current one. And what if you add co-drivers, secretaries or whatever later? Every time a new table for a new job? Again: There are many ways to build your model. Choose the one that suits you best.

说了这么多，你仍然可以考虑将你的表“用户”减少到公共字段，并让新表“管理员”，“收件人”，“驱动程序”和“conglomeration_managers”持有其他字段。这使您更接近现实，并使包的链接更清晰。然而，它是以与当前模型不同的模型为代价的。如果你以后添加联合司机，秘书或其他什么呢？每次换新工作的新表？再说一遍：构建模型的方法有很多种。选择最适合你的。

I hope my advice helps you think it all through.

我希望我的建议可以帮助你全面思考。