应用继承与我的SQL表结构 [英] Applying Inheritance with My SQL table structure

问题描述

我为教师和学生开发了一个移动应用程序...数据库包含教师，学生和校长等表。所有这些表都有不同的列，具体取决于角色。

I am developing a mobile app for teachers and students... the database contains tables like teacher, student and principal.All these tables have different columns depending upon the role.

现在我想要一个名为posts的表来存储所有这些用户的帖子。

Now I want a table called posts that stores posts from all these users.

问题1：我不能定义一个名为users的表，因为这些角色有不同的列/属性。

Problem 1 : I can not define a table called users with a column ROLE to identify a particular user because these roles have different columns/properties.

另外...对于这些用户我定义了2列，一个是id是唯一的所有教师和学生...和user_id像T23（对于id为23的教师）和S44（对于id为44的学生）。

Also... for these users I am defining 2 columns, one is id that is unique for all teachers and students...and user_id like T23 (for teachers with id 23) and S44 (for students with id 44).

现在我创建了包含以下列的帖子表： id，user_is，description，date_created，Status。

Now I have created posts table with following columns: id, user_is, description, date_created, Status.

现在我不确定...如何
获取所有帖子以及用户名Join应该在这里）

Now I am not sure... how to Get all the Posts along with the user name (How Join should work here)

推荐答案

我认为更好的解决方案是USER-GROUP-ROLE表。

I think the better solution is USER-GROUP-ROLE tables.

USER很明显;这些是你的学生和老师。

USER is obvious; these are your students and teachers.

GROUP是多对多JOIN表，用于将USER与ROLE相关联。 （A GROUP可以有许多USER和许多ROLE）。

GROUP is many-to-many JOIN table that relates USER to ROLE. (A GROUP can have many USERs and many ROLEs).

ROLE将是通常的嫌疑人：读/写/删除/执行。

ROLE would be the usual suspects: read/write/delete/execute.

您将角色分配给组，然后将用户添加到组。这样，您可以允许学生只读访问他们的记录，但教师可以读/写访问所有学生的记录。

You assign roles to groups, then add users to groups. That way you can allow a student read-only access to their records, but teachers can have read/write access to all their students' records.

您可以将教师管理员：教师只能读/写他们教的学生，但管理员应该能够读/写学校里的所有学生。
这种安排的另一个优点是，您不必每次更改角色时都编辑USER行。您所做的就是修改分配给他们的GROUP。

You can partition teachers from administrators: teachers can only read/write to students they teach, but administrators should be able to read/write all students in the school. The other advantage to this arrangement is that you don't have to edit USER rows every time they change roles. All you do is modify the GROUP they are assigned to.

这篇关于应用继承与我的SQL表结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！