There's always the library example as well (A library has many books, each book has an author and publisher which can be pushed into separate tables as you normalise)
I'm especially trying to find some good examples for normalisation: tables that don't immediately fit 3NF and BCNF.
You can find normalized database schema samples here: http://www.microsoft.com/sqlserver/2005/en/us/express-starter-schemas.aspx. You can build them from the ground up to show your students the "way of normalization". Particularly look at the Contact Management schema. You can easily denormalize the schema and bring it back on 3NF or deeper.
Itzik Ben Gan's new book, Microsoft SQL Server 2008: T-SQL Fundamentals, has a very basic example which you can see is derived from a simplified Northwind database.
Having taught database classes, I would suggest forgetting about design until the basics of querying are mastered. People understand the need for normalization much better once they understand how to get data out of a database. If you start with normalization and design, you will lose most of the students for the rest of the class. Design should be the very last module of a database course, but all the textbooks I ever reviewed started with it.
Even better make them query both good and bad database designs when learning querying and then they will really understand how painful bad design is when it is time to teach design.
One concept that I will never forget is the whole "plural" versus "singular" thing with naming. A great mentor I had way back once told me that you should design your table names as plural and your column names as singular and that you never create temporal-specific names for column names. Temporal Names examples are NutsSold1998, NutsSold1999, NutsSold2000, etc. Never add a year or month or week number or time, etc to a column name.
Table Name Examples: Employees (not Employee) Parts (not Part) Students (Student)
Column Name Examples: EmployeeID (not EmployeesID or EmployeeIDS, etc) PartID (not PartsID or PartIDS, etc) StudentID (not StudentsID or StudentIDS, etc)
And to pay attention to the correct usage of ID, Code, Key, Number, etc... I was always taught to not use "Key" in the name of a Column unless it was an actual table Key (primary or foreign, but not necessarily alternate).. Most of the time appending "ID" will be a better choice than appending "Number" or "Code", but it all depends on the context.
And that comes with time and experience in designing tables, reading good materials such as the book Database Design For Mere Mortals and Data Modeling for Everyone. Also, spending lots of time looking at good designs and taking them apart. Its definitely a craft and you only get better with time and practice.
Here's an example that dates back to my own University days - it was used both as a database design challenge and as an Object Oriented design challenge.
Not all information was revealed at once - part of the challenge was to see how to adapt a design to handle new requirements, and how proper normalisation made this easier.
Assume you have to design a database for a university/college situation and want to handle enrollments.
You have the Courses taught. Each course has a title and a regular timeslot each week.
Each Course has a Lecturer who presents the course.
Each Course has many Students who study the course.
Each Course has one or more Tutors who help students with their study. You don't need to track which tutors help which students.
Some courses have multiple regular timeslots.
Some courses have multiple Lecturers.
Lecturers and Tutors are paid, which means we need to track some information for tax purposes. The Tax department doesn't care what they were paid for - they expect us to have a single record per person.
On some courses, the Lecturer works as a Tutor as well, to get a close up view of how some students are handling the material.
Some Tutors are also Lecturers, on other courses.
To be a Tutor on a course, you have to have been a Student on that course at an earlier time.
Not every student will receive credit for passing the course - some are just auditing the course without a need for credit.
A student who fails a course may attend the course again later on. We need to keep records of every attempt.
发布评论
评论（9）
也总是有图书馆的例子（图书馆有很多书，每本书都有一个作者和出版商，可以在标准化时将其推入单独的表中）
There's always the library example as well (A library has many books, each book has an author and publisher which can be pushed into separate tables as you normalise)
关于：
您可以在此处找到标准化数据库架构示例：http://www.microsoft.com/sqlserver/2005/en/us/express-starter-schemas.aspx。 您可以从头开始构建它们，向您的学生展示“规范化之道”。 特别是查看联系人管理架构。 您可以轻松地对模式进行非规范化并将其恢复到 3NF 或更深入。
Re:
You can find normalized database schema samples here: http://www.microsoft.com/sqlserver/2005/en/us/express-starter-schemas.aspx. You can build them from the ground up to show your students the "way of normalization". Particularly look at the Contact Management schema. You can easily denormalize the schema and bring it back on 3NF or deeper.
Itzik Ben Gan 的新书《Microsoft SQL Server 2008：T-SQL 基础知识》有一个非常基本的示例，您可以看到它源自简化的 Northwind 数据库。
Microsoft SQL Server 2008：T-SQL 基础知识作者支持页面
Itzik Ben Gan's new book, Microsoft SQL Server 2008: T-SQL Fundamentals, has a very basic example which you can see is derived from a simplified Northwind database.
Microsoft SQL Server 2008: T-SQL Fundamentals author support page
我似乎记得学生/班级是一个经典，你也可以把成绩放在那里，让它变得更复杂一点。
学生可以参加许多课程
课程有很多学生
对于学生参加的每个课程，他们都可以获得成绩
最初，您可以在一张表中执行此操作，然后将其非规范化为三。
I seem to remember student / class as being a classic, you can put grades in there as well to make it a bit more complex.
Student can atted many classes
Classes have many students
For eachclass a student attends they can have a grade
Initially you can do this in one table and denormalise to three.
题外话
在教过数据库课程后，我建议忘记设计，直到掌握查询的基础知识。 一旦人们了解了如何从数据库中获取数据，人们就会更好地理解标准化的必要性。 如果你从规范化和设计开始，你将失去班级剩下的大部分学生。 设计应该是数据库课程的最后一个模块，但我复习过的所有教科书都是从它开始的。
更好的是让他们在学习查询时同时查询好的和坏的数据库设计，然后当他们教授设计时，他们就会真正理解坏的设计是多么痛苦。
off topic
Having taught database classes, I would suggest forgetting about design until the basics of querying are mastered. People understand the need for normalization much better once they understand how to get data out of a database. If you start with normalization and design, you will lose most of the students for the rest of the class. Design should be the very last module of a database course, but all the textbooks I ever reviewed started with it.
Even better make them query both good and bad database designs when learning querying and then they will really understand how painful bad design is when it is time to teach design.
电子商务/购物车设计很好，因为大多数人都理解这个概念，并且您可以将其推向许多不同的方向。
你可以做一些简单的事情，比如 cart、cart_items、users、orders、order_items 等。
然后你可以更深入地处理 user_addresses、user_emails、items、item_details、item_history 等。
这可以提供很多很好的辩论，因为有很多判断来电。
E-commerce/shopping cart design is good because most people understand the concept and you can push it in many different directions.
You can do simple things like cart, cart_items, users, orders, order_items, etc.
Then you can go deeper with user_addresses, user_emails, items, item_details, item_history, etc.
This can provide a lot of good debate because there are lots of judgment calls.
我永远不会忘记的一个概念是命名中的“复数”与“单数”。 我很久以前的一位伟大导师曾经告诉我，你应该将表名设计为复数，将列名设计为单数，并且永远不要为列名创建特定于时间的名称。 时间名称示例包括 NutsSold1998、NutsSold1999、NutsSold2000 等。切勿在列名称中添加年、月、周数或时间等。
表名称示例：
员工（非员工）
零件（非零件）
学生（Student）
栏名称示例：
EmployeeID（不是EmployeesID或EmployeeIDS等）
PartID（不是 PartID 或 PartIDS 等）
StudentID（不是 StudentsID 或 StudentIDS 等）
并注意 ID、代码、密钥、数字等的正确使用...我总是被教导不要在列名称中使用“Key”，除非它是实际表键（主键或外键，但不一定是备用键）。大多数情况下，附加“ID”将是比附加“数字”或“代码”更好的选择，但这完全取决于上下文。
这需要设计表格的时间和经验，阅读好的材料，例如Database Design For Mere Mortals< /a> 和适合所有人的数据建模。 此外，还要花大量时间研究好的设计并将它们拆开。 这绝对是一门手艺，只有随着时间和练习，你才会变得更好。
One concept that I will never forget is the whole "plural" versus "singular" thing with naming. A great mentor I had way back once told me that you should design your table names as plural and your column names as singular and that you never create temporal-specific names for column names. Temporal Names examples are NutsSold1998, NutsSold1999, NutsSold2000, etc. Never add a year or month or week number or time, etc to a column name.
Table Name Examples:
Employees (not Employee)
Parts (not Part)
Students (Student)
Column Name Examples:
EmployeeID (not EmployeesID or EmployeeIDS, etc)
PartID (not PartsID or PartIDS, etc)
StudentID (not StudentsID or StudentIDS, etc)
And to pay attention to the correct usage of ID, Code, Key, Number, etc... I was always taught to not use "Key" in the name of a Column unless it was an actual table Key (primary or foreign, but not necessarily alternate).. Most of the time appending "ID" will be a better choice than appending "Number" or "Code", but it all depends on the context.
And that comes with time and experience in designing tables, reading good materials such as the book Database Design For Mere Mortals and Data Modeling for Everyone. Also, spending lots of time looking at good designs and taking them apart. Its definitely a craft and you only get better with time and practice.
另一个好的模型是发票项目模型，因为“最佳选择”取决于各种因素：
看一下这个数据模型：
发票
InvoiceItem
应用程序功能有：
假设您平均每张发票有 5 个项目，每天有 100 张发票，您最终会每天执行以下操作：
所以总计 = 1800 次操作/天，假设读取和写入具有相同的成本。
如果您在实体“Invoice”上添加“TotalAmount”属性，情况会有所不同：
总共 800 次操作:)
Another good model is the invoice-item model, because the "best choice" depends on various factors:
Look at this data model:
Invoice
InvoiceItem
Application funcitonalities are:
Say you have an average of 5 items per invoice and 100 invoices per day, you end up doing, for each day:
so total is = 1800 operations / day, assuming reads and writes have the same cost.
If you add "TotalAmount" attribute on entity "Invoice", situation is a bit different:
with a total of 800 operations :)
这是一个可以追溯到我大学时代的示例 - 它既用作数据库设计挑战又用作面向对象设计挑战。
并非所有信息都会立即披露 - 挑战的一部分是了解如何调整设计来处理新需求，以及适当的标准化如何使这变得更容易。
假设您必须为大学/学院的情况设计一个数据库并想要处理注册。
您已教授课程。 每门课程都有一个标题和每周的固定时段。
每门课程都有一位讲师来介绍该课程。
每门课程都有许多学习该课程的学生。
每门课程都有一名或多名导师帮助学生学习。 您不需要跟踪哪些导师帮助哪些学生。
有些课程有多个固定时段。
有些课程有多名讲师。
讲师和导师是有报酬的，这意味着我们需要跟踪一些信息以用于税务目的。 税务部门并不关心他们的工资是多少——他们希望我们每人都有一份记录。
在某些课程中，讲师还担任导师，以近距离了解一些学生如何处理材料。
一些导师同时也是其他课程的讲师。
要成为某门课程的导师，您必须之前是该课程的学生。
并不是每个学生都会通过课程获得学分——有些学生只是旁听课程而不需要学分。
未通过课程的学生可以稍后再次参加该课程。 我们需要保留每一次尝试的记录。
Here's an example that dates back to my own University days - it was used both as a database design challenge and as an Object Oriented design challenge.
Not all information was revealed at once - part of the challenge was to see how to adapt a design to handle new requirements, and how proper normalisation made this easier.
Assume you have to design a database for a university/college situation and want to handle enrollments.
You have the Courses taught. Each course has a title and a regular timeslot each week.
Each Course has a Lecturer who presents the course.
Each Course has many Students who study the course.
Each Course has one or more Tutors who help students with their study. You don't need to track which tutors help which students.
Some courses have multiple regular timeslots.
Some courses have multiple Lecturers.
Lecturers and Tutors are paid, which means we need to track some information for tax purposes. The Tax department doesn't care what they were paid for - they expect us to have a single record per person.
On some courses, the Lecturer works as a Tutor as well, to get a close up view of how some students are handling the material.
Some Tutors are also Lecturers, on other courses.
To be a Tutor on a course, you have to have been a Student on that course at an earlier time.
Not every student will receive credit for passing the course - some are just auditing the course without a need for credit.
A student who fails a course may attend the course again later on. We need to keep records of every attempt.