数据库设计模式对两个实体有帮助

Let us say I have two different entities on a site.

假设我在一个网站上有两个不同的实体。

Teachers and Students.

教师和学生。

They both login to the website.Right now I have the following tables

他们都登录了网站。现在我有以下表格

**Users**

 - id
 - email
 - password

**Students**

 - GPA
 - SAT
 - ACT
 - user_id references Users ID

**Teachers**

 - user_id references Users ID
 - classroom_no
 - salary
 - average_class_size

So when a Student registers I add a row in both the Users and Teacher table.

因此，当一个学生注册时，我在用户表和教师表中添加一行。

When a Teacher registers, I add a row in both the Users and Teacher table.

当老师注册时，我在用户表和教师表中添加一行。

Is it better to just have ONE table Users with the following fields?

仅仅拥有一个具有以下字段的表用户是否更好?

**Users:**

 - id
 - ACT
 - GPA
 - SAT
 - average_class_size
 - salary
 - classroom_no
 - role (0 = teacher 1 = student)

even though some rows would refer to teachers (and thus not use the ACT, SAT, GPA fields)

尽管有些行是指老师(因此不用ACT, SAT, GPA)

? ?

Thanks!

谢谢!

5 个解决方案

#1

If you are not familiar with database normalization, please read this.

如果您不熟悉数据库规范化，请阅读本文。

Unless performance is a serious issue, I would not look at using a single table for this structure. As your project goes one, you will more than likely add more fields to each role and possibly add more roles as well. It will become increasingly more difficult to maintain this.

除非性能是一个严重的问题，否则我不会考虑为这个结构使用单个表。随着项目的进行，您可能会为每个角色添加更多的字段，并可能添加更多的角色。维持这种局面将变得越来越困难。

I would actually go a step further and introduce another concept called role into your application, along with the appropriate data model to persist it.

实际上，我将更进一步，在您的应用程序中引入另一个名为role的概念，以及用于持久化它的适当数据模型。

Your tables should be:

你的表应该是:

users:
 - id
 - email
 - password

roles:
 - id
 - type

user_roles:
 - user_id
 - role_id

role_teacher:
 - user_id
 - classroom_no
 - salary
 - average_class_size

role_student:
 - user_id
 - GPA
 - SAT
 - ACT

Having a separate role table will let you separate your authentication table (users) from the rest of the data. This might become important if a user can be both a teacher and a student.

拥有一个单独的角色表将使您可以将身份验证表(用户)与其他数据分开。如果用户既是老师又是学生，这一点可能会变得很重要。

This should give you a basic understanding of how to structure your database.

这将使您对如何构建数据库有一个基本的了解。

You may find that you will begin to model some of the other concepts like classes in your system. So adding the appropriate tables may look something like:

您可能会发现，您将开始建模其他一些概念，如系统中的类。因此，添加适当的表可能会如下所示:

role_teacher:
 - user_id
 - salary

classroom:
 - id
 - capacity

class:
 - id
 - name
 - teacher_id
 - classroom_id
 - start_date
 - end_date

class_enrollment:
 - class_id
 - student_id
 - enrollment_date
 - grade

Items like the teachers class room now moves to the class, allowing for the teacher to teach multiple classes (maybe you need to re-add the concept of "home room"). The teachers average class size then just becomes a calculation (though you may want to still store it for the sake of efficiency).

像“教师教室”这样的项目现在转移到课堂上，允许教师教授多个课程(也许你需要重新添加“家庭教室”的概念)。教师的平均班级规模就变成了一种计算(尽管为了提高效率，您可能还想保留它)。

Hope this helps guide you in the right direction!

希望这能帮助你找到正确的方向!

#2

You should to go with Teachers and Students tables. Tables in database should reflect real world model (whenever it's possible of course). Merging everything into single User table brings very little value and introduces following issues:

你应该和老师和学生一起去。数据库中的表应该反映真实世界的模型(当然，只要有可能)。将所有内容合并到一个用户表中几乎没有什么价值，并引入以下问题:

redundancy (teacher doesn't need ACT, GPA and so forth fields as you mentioned)
冗余(老师不需要ACT, GPA等你提到的领域)
complexity (you'll need extra logic to determine whether user is student or teacher)
复杂性(您需要额外的逻辑来确定用户是学生还是老师)
ambiguity (is user a system user, or real world person data, or just login information...?), term is far too generic
模棱两可(用户是系统用户，还是真实世界的人数据，或者只是登录信息…?

On top of that, I'd rename Users table to UserLogins - it indicates its purpose more clearly.

最重要的是，我将Users表重命名为UserLogins——它更清楚地表明了它的目的。

#3

My feeling is that it's better the way you have it - you have to maintain both tables so that they're in sync, but you've better encapsulated the role of "User", "Student", and "Teacher" this way. It also leaves you a better maintenance footprint - what if you want to allow a "Parent" to log in with a reference to their child? What about a "Principal"? These are all users, but would quickly gum up the unified table.

我的感觉是，这样更好——你必须维护这两个表，使它们保持同步，但你最好这样封装“用户”、“学生”和“老师”的角色。它还为您留下了更好的维护空间——如果您希望允许“父类”登录到它们的子类的引用中，该怎么办?“主要”呢?这些都是用户，但很快就会把统一的表搞乱。

#4

There are a couple of ways to do this, but I think one table for users is the right way to go. You could have the empty columns - it doesn't look the greatest, but if it works for you, it would allow minimal coding to get the values you need.

有两种方法可以做到这一点，但是我认为用户使用一个表是正确的。你可以有空列——它看起来不是最大的，但是如果它适合你，它将允许最小的编码来获得你需要的值。

Another option might be to have an attributes table { UserId, Attribute, Value }, then for example, you could have: User1 GMAT 710, User1 ACT 30, etc. So you could then get a list of all attributes for a user by select * from attributes where UserId = user.UserId or some such. More code overhead, much cleaner. This would also allow you to add more attributes in the future for whatever purpose, and keep your database the same.

另一个选项可能是有一个属性表{UserId, Attribute, Value}，然后，例如，您可以有:User1 GMAT 710, User1 ACT 30等等。用户标识或一些这样的。更多的代码开销，更干净。这也允许您将来为任何目的添加更多的属性，并保持数据库不变。

#5

If one teacher can take multiple classes, then your design fails, as the teacher table will have a multiple entries..

如果一个老师可以选修多个课程，那么你的设计就失败了，因为教师表将有多个条目。

Instead Create one Teacher Table where we can store Teacher Table, create one intermedite table, where we can store the Teacher and His classes.

相反，创建一个教师表，我们可以存储教师表，创建一个中间表，我们可以存储教师和他的类。

Similarly, Teacher and User table should be separate.. Let a separate User Table exists where we can store the Use details..

同样，教师和用户表应该分开。让一个单独的用户表存在于我们可以存储使用细节的地方。

#1