数据库规范化 - 合并/组合表 [英] database normalization - merge/combine tables
问题描述
请考虑以下情况。
我们有一个0NF表
StudentTeacherTable：
StudentName StudentDepartment StudentDepartmentAdd TeacherName TeacherDepartment TeacherDepartment添加
John CS伦敦Dave Eng，CS牛津
Mike CS伦敦Dave Eng，CS牛津
Chris Eng牛津Dave Eng，CS牛津
理想情况下，如
学生表：
StudentName部门TeacherName
John CS Dave
Mike CS Dave
Chris Eng Dave
教师表： / p>
名称
Dave
TeacherDepartment表：
TeacherName DepartmentName
Dave CS
Dave ENG
部门表：
code>名称地址
CS伦敦
ENG牛津
如果我遵循归一化到3NF。
我将获得
学生表：
Department TeacherName
John CS Dave
Mike CS Dave
Chris Eng Dave
DepartmentForStudent表：
姓名地址
CS伦敦
ENG牛津
教师表：
Dave
TeacherToDepartment表：
TeacherName DepartmentName
Dave CS
Dave ENG
$ b b
DepartmentForStudent表：
姓名地址
CS伦敦
ENG牛津
我的问题是，在数据库规范化（1NF，2NF，3NF等）的哪一步，我可以合并/组合studentDepartement与teacherDepartment列转换成一个表以导出上面的规范化形式？
换句话说，遵循规范化规则。我将最终拥有StudentDepartment表和TeacherDepartment表，而不是学生和教师的一个Department表。
与规范化无关。你问的问题，如果不是物理地连接类似类型和相同的属性集合的表。归一化在这个问题上没有偏好。基本上没有错误或权利。这是更多关于平衡权衡根据具体的设计设置：
选项1：有多个表（如你在你的例子中所示）：
优点：$ b $ b - 显式数据库设计 - >易于阅读
- 更低的内存/磁盘空间需要，因为不需要类型列
cons：
- 当使用代理或其他非自然键：没有唯一的交叉表标识符，这可能使潜在的变化需要改变难以管理
- 查看所有表需要大量的联合（expecially如果超过两个表）
选项2：有一个带有附加类型列的表：
选项1相反的pro和cons
$
2个例子：
G ***可能会找到很多资源。 b $ b存储分层数据（例如，具有类型与多个表的单表，1：1键和差异...）
在关系数据库设计模式？
http://sqlmag.com/sql-server/trouble-type-tables
Please consider the following scenario.
We have a 0NF table
StudentTeacherTable:
StudentName StudentDepartment StudentDepartmentAdd TeacherName TeacherDepartment TeacherDepartmentAdd
John CS London Dave Eng, CS Oxford
Mike CS London Dave Eng, CS Oxford
Chris Eng Oxford Dave Eng, CS Oxford
Ideally after normalization I would like to have tables like
Student Table:
StudentName Department TeacherName
John CS Dave
Mike CS Dave
Chris Eng Dave
Teacher Table:
Name
Dave
TeacherDepartment Table:
TeacherName DepartmentName
Dave CS
Dave ENG
Department Table:
Name Address
CS London
ENG Oxford
However, if I follow normalization to the 3NF. I will get
Student Table:
StudentName Department TeacherName
John CS Dave
Mike CS Dave
Chris Eng Dave
DepartmentForStudent Table:
Name Address
CS London
ENG Oxford
Teacher Table:
Name
Dave
TeacherToDepartment Table:
TeacherName DepartmentName
Dave CS
Dave ENG
DepartmentForStudent Table:
Name Address
CS London
ENG Oxford
My question is that in which step in database normalization (1NF,2NF,3NF etc) I can merge/combine the studentDepartement with teacherDepartment columns into one table to derive the normalized form above?
In other words, following normalization rules. I will end up having a StudentDepartment table and a TeacherDepartment table rather than one Department table for both Student and Teacher
Your question has nothing to do with normalization. You are asking the question, if of if not to physically join tables of similar types and same sets of attributes. Normalization has no preference in that matter. And basically there is no wrong or right. This is more about balance trade-offs according to a specific design setup:
option 1: have multiple tables (as you did show in you example): pros: - explicit database design -> easy to read - lower memory/disk space need as no type column is needed
cons: - when using surrogate or other no-natural keys: no unique cross table identifier which may make potential upcomming needs for change hard to manage - viewing accross all tables requires lots of unions (expecially if more than two tables)
option 2: have one table with an additional type column: pro's and cons in opposite direction of option 1
G*** may find you lots of resources to that topic.
2 examples: Storing hierarchical data (e.g. single table with type vs multiple tables with 1:1 key and differences...) in Relational Database Design Patterns?
http://sqlmag.com/sql-server/trouble-type-tables
这篇关于数据库规范化 - 合并/组合表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
