MySQL 学校管理系统的数据库规范化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7991112/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Database normalization for School Management System
提问by WebNovice
I am creating system for a school management system and come up with the attached database schema.
我正在为学校管理系统创建系统并提出附加的数据库模式。
Following is how the system works:
以下是系统的工作原理：
- A school has many students and teachers. It has also many courses(subjects) taught. A grade level can have many courses assigned. These courses in turn will be assigned to the students in that particular grade.
- The levels of students are categorized into grades and sections. A student can be in Grade 5, but if grade 5 students are huge in number, they are divided into sections. eg: Grade 5 section A, Grade 5 section B.
- Students are placed in unique classrooms. A classroom will be unique throughout. Classroom of Grade 5 Section A of year 2010 will be different from Classroom of Grade 5 Section A of year 2011.
- Students are assigned parents. A parent can have more than one student in the school.
- One or more classrooms can be assigned to a teacher.
- Attendance for students are taken by their teacher on a daily basis
- There will be many types of exams. Exam results are stored for each subject (course).
- 一所学校有很多学生和老师。它还开设了许多课程（科目）。一个年级可以分配多门课程。这些课程将依次分配给该特定年级的学生。
- 学生的水平分为年级和班级。一个学生可以在 5 年级，但如果 5 年级学生人数众多，他们就会被分成几个部分。例如：5 年级 A 部分，5 年级 B 部分。
- 学生被安置在独特的教室里。一个教室将是独一无二的。2010年五年级甲组课堂与2011年五年级甲组课堂有所不同。
- 学生被分配家长。一位家长可以在学校有多个学生。
- 一个或多个教室可以分配给一名教师。
- 学生的出勤由他们的老师每天进行
- 会有很多类型的考试。存储每个科目（课程）的考试结果。
I am a beginner in database normalization and would be glad if anyone could give me some hints if the database looks alright or not.
我是数据库规范化的初学者，如果有人能给我一些提示，如果数据库看起来不错，我会很高兴。
EDIT:
编辑：
Also, there will only be one point of login. In the above case, during login, a user will have to select the type of user from a dropdown list. That dropdown selection will be used to query to respective table to login to the system. Another alternative is to use a common usertable, which will store the user_id, email, password, last_login_date, last_login_ipbut will store other details in respective tables such as student, parent, teacher. So, what is the preferred/correct way to implement it?
此外，将只有一个登录点。在上述情况下，在登录期间，用户必须从下拉列表中选择用户类型。该下拉选择将用于查询相应的表以登录系统。另一种选择是使用公共user表，该表将存储 ，user_id, email, password, last_login_date, last_login_ip但将在相应的表中存储其他详细信息，例如student, parent, teacher. 那么，实现它的首选/正确方法是什么？
回答by APC
You don't model GRADE_SECTIONS at all.
您根本没有对 GRADE_SECTIONS 建模。
Unless your school has a massive programmr of demolition and construction every summer holiday the classrooms will be the same. It is the assignmentswhich change each year. So CLASSROOMS should be assigned to a separate GRADE_SECTION entity, instead of merging SECTIONS and CLASSROOMS as you do now.
除非你的学校在每个暑假都有一个大规模的拆除和建设计划，否则教室是一样的。这是每年都在变化的任务。因此，应该将 CLASSROOMS 分配给单独的 GRADE_SECTION 实体，而不是像现在那样合并 SECTIONS 和 CLASSROOMS。
Students should be assigned to GRADE_SECTIONS not CLASSROOMS.
学生应该被分配到 GRADE_SECTIONS 而不是 CLASSROOMS。
COURSES should have many EXAMS rather than many EXAM_RESULTS. It simply doesn't make sense that a French Exam could be taken by students learning Maths and Russian.
COURSES 应该有很多 EXAMS 而不是很多 EXAM_RESULTS。学习数学和俄语的学生可以参加法语考试是没有意义的。
