数据库设计：多个用户实体。单个或多个表

I am building a PHP app that has 3 basic entity types: Coach, Student, Lesson, where coaches create digital lessons for students. I'm using MySQL and innoDB tables.

我正在构建一个包含3种基本实体类型的PHP应用程序:Coach,Student,Lesson,其中教练为学生创建数字课程。我正在使用MySQL和innoDB表。

Requirements

1. Coach and student login.

教练和学生登录。

2. Coach can deliver a digital lesson specifically for a single student.

教练可以专门为单个学生提供数字课程。

I'm unsure what is the best DB schema to use given the requirements. Here are two options:

我不确定在给定要求的情况下最好使用什么DB模式。这有两个选择:

Option 1
User (PK id, user_type (coach or student), firstname, lastname, email, password, etc...)
Lesson (PK id, FK coach_user_id (ref: User.id), FK student_user_id (ref: User.id), lesson_name, etc…)

选项1用户(PK id,user_type(教练或学生),名字,姓氏,电子邮件,密码等...)课程(PK id,FK coach_user_id(ref:User.id),FK student_user_id(ref:User.id) ),lesson_name等...)

Pros:
- One user table
- Each user has a unique ID and email
- Makes login auth easy with single table

优点: - 一个用户表 - 每个用户都有一个唯一的ID和电子邮件 - 使用单个表可以轻松登录auth

Cons:
- No validation of user_type when a coach or student User.id is recorded as a FK in the lesson table. This problem will reoccur in any new table where a coach or student User.id needs to be recorded as a FK.
- Potential polymorphism issues and the need to normalise down the track.

缺点: - 当教练或学生User.id在课程表中记录为FK时,不验证user_type。此问题将在任何需要将教练或学生User.id记录为FK的新表中重现。 - 潜在的多态性问题以及规范化轨道的必要性。

Option 2
Coach (PK id, firstname, lastname, email, password, etc...)
Student (PK id, firstname, lastname, email, password, etc...)
Lesson (PK id, FK coach_id (ref: Coach.id), FK student_id (ref: Student.id), lesson_name, lesson_text, etc…)

选项2教练(PK id,名字,姓氏,电子邮件,密码等...)学生(PK id,名字,姓氏,电子邮件,密码等...)课程(PK id,FK coach_id(ref:Coach。 id),FK student_id(ref:Student.id),lesson_name,lesson_text等...)

Pros:
- Normalised DB schema. Independent coach, students entity tables.
- No user type validation issues. Coach ID and student ID FK's point independently to Coach.id and Student.id respectively.

优点: - 规范化的数据库架构。独立教练,学生实体表。 - 没有用户类型验证问题。教练ID和学生ID FK分别独立于Coach.id和Student.id。

Cons:
- Coach and student can have the same ID. (This can be solved though with ID prefixes e.g. C1001, S1001)
- Coach and student can have the same email.
- Login auth involves querying two 2 tables for single login page, or creating 2 different login pages and auth request types.

缺点: - 教练和学生可以拥有相同的身份证。 (这可以通过ID前缀解决,例如C1001,S1001) - 教练和学生可以拥有相同的电子邮件。 - 登录身份验证涉及查询两个2个表用于单个登录页面,或创建2个不同的登录页面和身份验证请求类型。

I'm really torn which is the best way to go. Is there a better way to do this?

我真的被撕裂了,这是最好的方式。有一个更好的方法吗?

2 个解决方案

#1

In my opinion, both of your approaches would work. The first one is more universal, and capable of fitting various currently unknown requirements . If you choose it, I'd recommend to add concept of Role to the model - "user_type" is a role, and one user can be associated with different roles [at the same time]. Also, "The Data Model Resource Book" by Len Silverston is a great resource .

在我看来,你的两种方法都有效。第一个是更普遍的,并且能够适应各种当前未知的要求。如果你选择它,我建议在模型中添加Role的概念 - “user_type”是一个角色,一个用户可以[同时]与不同的角色相关联。此外,Len Silverston的“数据模型资源手册”是一个很好的资源。

However, you may not always want your schema to be too general. You listed pros and cons for 2 approaches on very low level; I think that practicability is more important than particular technical issues (which can be overcome ). I'd put it that way :

但是,您可能并不总是希望您的架构过于笼统。您列出了两种方法的优缺点,非常低;我认为实用性比特定技术问题(可以克服)更重要。我这么说吧:

1) Pros :

1)优点:

• easy to accommodate new features without major changes to schema

无需对架构进行重大更改即可轻松容纳新功能

• very flexible
• easy to build cubes on top of the schema

易于在架构之上构建多维数据集

• fits long term projects

适合长期项目

Cons :

• requires more resources (experienced DBA/Data Model specialist[s], comparatively longer design time )

需要更多资源(经验丰富的DBA /数据模型专家[s],设计时间相对较长)

• way more complex than (2)

比(2)更复杂的方式

2) Pros :

2)优点:

• fast delivery of first working version

快速交付第一个工作版本

• quite easy for understanding even by non-technical people (until it grows up)

甚至非技术人员也很容易理解(直到它长大)

• fits either small projects or projects with well-defined domains which [almost] never change

适合小型项目或具有明确定义的领域的项目,这些领域几乎永远不会改变

Cons :

• never ending refactoring of schema as new requirements come

随着新要求的到来,永远不会结束架构的重构

• if project lives long enough, database becomes full of "not used anymore" columns(or other db objects) nobody wants to touch

如果项目足够长,数据库就会充满“不再使用”列(或其他数据库对象),没有人想要触摸

• harder to enforce integrity

更难以执行诚信

I hope it makes sense and helps you to make the right decision which fits your needs.

我希望它有意义并帮助您做出符合您需求的正确决定。

#2

Option 1 looks better to me.

选项1看起来对我来说更好。

It will simplify your code when you don't care to distinguish students from coaches, and will be pretty much the same as option 2 if you want to distinguish them.

如果您不想区分学生和教练,它会简化您的代码,如果您想区分它们,它将与选项2几乎相同。

If you really need to validate the foreign keys you can use triggers to check if its a coach or not.

如果您确实需要验证外键,可以使用触发器来检查它是否是教练。

I'm not sure what you mean by "Potential polymorphism issues and the need to normalise down the track.".

我不确定你的意思是“潜在的多态性问题以及在轨道上规范化的必要性”。