围绕遗传结构设计关系 [英] Designing relationships around an inheritance structure

问题描述

我有一个关于如何最好地组织我的数据库的概念性问题。

I have a conceptual question regarding how best to organise my database.

目前我有四个核心表用户，教师，学生和通知。然而，教师和学生表继承自用户表格包含外键 user_id 。

Currently I have four core tables users, teachers, students and notifications. However both the teachers and students tables inherit from the users table so contain the foreign key user_id.

通知你可能猜到的表是指通知。

The notifications table as you might have guessed refers to notifications. These need to appear for all users that belong to an employee group i.e. under the employment of another.

学生和老师都可以使用其他用户。

Both students and teachers can employ other users.

所以关键是我需要一个雄辩的建模方法。代码的基本工作流程如下：

So the crux is I need an eloquent way of modelling this. The basic workflow of the code would be the below:

getCurrentUser->getAllEmployer(s)->getNotifications

这是Laravel雄辩，我习惯于 $ user-> employer（） - >通知;

This is the Laravel Eloquent I'm used to $user->employers()->notifications;

不幸的是，它不像在这种情况下那样简单，雇主可以参考两个表。

Unfortunately it's not as simple as that as in this case an employer can refer to two tables.

所以我的选择如下。

1. 为$ $ c创建一个雄辩的关系$ c>学生和老师
   关系作为雇主。如果
   测试检查当前用户是否属于任何一个，并且此代码
   将被频繁重复，那么我需要写入的不足。


2. 添加一个 teacher_id 和 student_id 到
   用户表。然而，在每个
   记录中显然是多余的。由于新雇主实体的出现，需要添加其他列的机会非常可能为
   。


3. 创建一个 employer_employee 表，其中包含两个引用 user_id 的列。 SQL查询将LEFT JOIN student 和
   教师表与 employer_employee 表，然后一个JOIN
   与通知将返回所有相关的。然而，
   与
   其他选项相比，这么多加入可以减少查询的速度。


4. 我没有考虑过某些内容。

1. Create an Eloquent Relationship for both the student and teacher relationship as employers. The shortfall being I need to write if tests to check if the current user belongs to either and this code would be repeated frequently.
2. Add a teacher_id and student_id to the users table. However one would obviously be redundant in each record. The chance of needing to add other columns is very likely as well due to the emergence of new employer entities.
3. Create an employer_employee table that contains two columns both referencing a user_id. A SQL query would LEFT JOIN both student and teacher tables with the employer_employee table and then a JOIN with notifications would return all those relevant. However would so many joins reduce the speed of the query when compared with the other options.
4. Something I haven't considered.

我真的在寻找最有效率的可扩展解决方案。

I'm really looking for the most efficient, scalable solution.

任何帮助都不胜感激。如果你能澄清为什么你的答案是最有效率的可扩展解决方案，那将是一流的。

Any help is appreciated. If you could clarify why your answer is the most efficient scalable solution as well that would be superb.

推荐答案

有一个类似的问题此处使用Media超类型，并添加CD，VCR，DVD等的子类型。

There is a similar question here using a Media supertype and adding subtypes of CD, VCR, DVD, etc.

这是可扩展的，因为在创建BluRay子类型时，您可以创建包含BluRay特定数据的表，并将一个条目添加到MediaTypes表。现有数据或代码不需要更改 - 除了当然要添加与BluRay数据一起使用的代码。

This is scalable in that in creating, say, a BluRay subtype, you create the table to contain the BluRay-specific data and add an entry to the MediaTypes table. No changes needed for existing data or code -- except, of course, to add the code that will work with BluRay data.

在您的情况下，用户将是超类型表与教师和学生的子类型表。

In your case, Users would be the supertype table with Teachers and Students the subtype tables.

create table Users(
    ID      int not null auto_generating,
    Type    char( 1 ) check( Type in( 'T', 'S' )),
    -- other data common to all users,
    constraint PK_Users primary key( ID ),
    constraint UQ_UserType unique( ID, Type ),
    constraint FK_UserTypes foreign key( Type )
        references UserTypes( ID )
);
create table Teachers(
    TeacherID int not null,
    TeacherType char( 1 ) check( TeacherType = 'T' )),
    -- other data common to all teachers...,
    constraint PK_Teachers primary key( TeacherID ),
    constraint FK_TeacherUser foreign key( TeacherID, TeacherType )
        references Users( ID, Types )
);

学生表的构成将类似于教师表。

The makeup of the Students table would be similar to the Teachers table.

由于教师和学生都可以聘请其他老师和学生，所以包含这种关系的表格将会指向用户表。

Since both teachers and students may employ other teachers and students, the table that contains this relationship would refer to the Users table.

create table Employment(
    EmployerID    int not null,
    EmployeeID    int not null,
    -- other data concerning the employment...,
    constraint CK_EmploymentDupes check( EmployerID <> EmployeeID ),
    constraint PK_Employment primary key( EmployerID, EmployeeID ),
    constraint FK_EmploymentEmployer foreign key( EmployerID )
        references Users( ID ),
    constraint FK_EmploymentEmployee foreign key( EmployeeID )
        references Users( ID )
);

据了解，通知由雇主分组：

As I understand it, Notifications are grouped by employer:

create table Notifications(
    EmployerID    int not null
    NotificationDate date,
    NotificationData varchar( 500 ),
    -- other notification data...,
    constraint FK_NotificationsEmployer foreign key( EmployerID )
        references Users( ID )
);

查询应该足够简单。例如，如果用户想要查看其雇主的所有通知：

The queries should be simple enough. For example, if a user wanted to see all the notifications from his employer(s):

select  e.EmployerID, n.NotificationDate, n.NotificationData
from    Employment  e
join    Notifications n
    on  n.EmployerID = e.EmployerID
where   e.EmployeeID = :UserID;

当然是初步草图。修改是可能的。但是对于你的数字：

This is an initial sketch, of course. Refinements are possible. But to your numbered points:

1. 就业表将雇主与员工联系起来。唯一的检查，如果使用户雇主不能自己雇佣，但否则任何用户都可以是雇员和雇主。


2. Users表强制每个用户是老师（'T' ）或学生（'S'）。只有定义为T的用户才能被放置在教师表中，只有定义为S的用户才能被放置在学生表中。


3. ，不是教师和学生的表。但这是因为教师和学生既可以是雇主，也可以是雇员，而不是出于任何性能原因。一般来说，不要担心初始设计时的性能。您最关心的是数据完整性。关系数据库与联接非常好。 如果出现性能问题，请修复。不要重组您的数据来解决尚不存在的问题，可能永远不存在。


4. 嗯，试试看看它是如何工作的。

1. The Employment table relates employers to employees. The only check if to make user employers cannot employee themselves, but otherwise any user can be both an employee and employer.
2. The Users table forces each user to be either a teacher ('T') or student ('S'). Only users defined as 'T' can be placed in the Teachers table and only users defined as 'S' can be placed in the Students table.
3. The Employment table joins only to the Users table, not to both the Teachers and Students tables. But this is because both teachers and students can be both employers and employees, not for any performance reason. In general, don't worry about performance during the initial design. Your primary concern at this point is data integrity. Relational databases are very good with joins. If a performance issue should crop up, then fix it. Don't restructure your data to solve problems that do not yet exist and may never exist.
4. Well, give this a try and see how it works.

这篇关于围绕遗传结构设计关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！