在为应用程序设计数据库时，需要记住什么一般准则和最佳实践？ [英] What are the general guidelines and best practices to keep in mind while designing database for an application?

问题描述

我的问题是关于数据库建模。我试图在其他数据库设计问题中寻找这个问题，但没有找到它，所以这里问：

My questions is regarding Database Modeling. I tried to look for this question amongst other Database Designing questions on SO but haven't found it and so here am asking about:

什么是一般准则和最佳实践

What are the general guidelines and best practices to keep in mind while designing database for an application ?

在数据库设计概念上可获得的最佳资源/书/大学讲座是什么？

What are the best resources/books/University Lectures available on Database Design Concepts ?

谢谢。

推荐答案

只是我从经验中学到的一些东西（我敢肯定有些人会不同意，设计和编程数据库30多年，并已经看到愚蠢的设计的效果接近和个人）：

Just some things I've learned from experience (I'm sure some will disagree, but I've been querying and designing and programming databases for 30+years and have seen the effects of stupid design up close and personal):

在所有数据库设计中需要考虑三个关键事项 - 数据完整性（没有这个你基本上没有数据），安全性和性能。

There are three critical things to consider in all database design - data integrity (without this you essentially have no data), security and performance. All other considerations take a back seat to these three.

从未创建表格，无法唯一标识记录。

Never create a table without a way to uniquely identify a record.

真的很少真正的自然键，真正的工作作为主键，如果你不能控制是否会改变，不要使用它作为主键（你不真的想改变公司名称通过27个孩子表你吗？）。使用代理键。使用代理键不会免除您设置唯一索引的需要，如果您可以使用唯一的复合键。如果可以确定具有唯一组合的方法，请始终设置这些索引。重复的记录是应用程序存在的祸根。这似乎很明显，但从来没有考虑过名字是一个关键字段，名称不是，永远不会是唯一的。

There really are very few true natural keys that really work as a primary key, if you don't have control over whether it will change, do not use it as a primary key (you don't really want to change the company name through 27 child tables do you?). Use a surrogate key instead. Using a surrogate key does not exempt you from the need to set unique indexes if you could have used a unique composite key. Always set these indexes if you can determine a way to have a unique composite. Duplicate records are the bane of an application's existance. It seems obvious but never ever consider name to be a key field, names are not and never will be unique.

不要使用GUID作为主键，因为它可以杀死性能。如果你需要一个guid的复制也考虑有一个int或大int的主键。

Do not use a GUID as your primary key as it can kill performance. If you need a guid for replication also consider having an int or big int primary key.

不要设计，如果你将改变数据库后端，除非你知道前面你会这样做。几乎所有良好的性能调优技术都是针对数据库的，不会损害您自己调整数据库的能力，以满足不存在的需求。

Do not design as if you will be changing database backends unless you know up front you will be doing so. Virtually all the good techniques for performance tuning are database specific, don't harm your own ability to tune your database for a non-existant requirement.

避免使用值实体表结构。他们很难查询。

Avoid value-entity table structures. They are miserable to query.

将所有需要的东西添加到数据库设计中，以确保数据完整性，例如默认值，约束，触发器等，无用的数据。

Add all things you need to ensure data integrity into your database design, things like defaults, constraints, triggers, etc. are necessary to avoid having useless data. Do not rely on the application code to do this or you will be sorry.

其他人提到规范化，我同意你必须彻底理解这一点，即使你以后决定去正规化。

Others mentioned normalization, I agree you must understand this thoroughly even if you later decide to denormalize.

如果你想要任何类型的性能，不要在视图的顶部堆叠视图。我看到的每个数据库都是这样的，最终会出现一个巨大的性能问题。

Do not stack views on top of views if you want any kind of performance at all. Every database I've seen that does this is eventually a huge performance problem.

考虑你需要什么数据来管理数据库以及应用程序需要什么。如果你要认真对待数据库，你需要理解数据库审计，你的数据库应该实现方法来找出谁做了什么改变，什么时候什么旧数据。你会感谢我第一次有人恶意更改数据，或者有人不小心删除了表中的所有记录。

Consider what data you will need to manage the database as well as what the application needs. If you are going to be serious about databases you need to understand database auditing and your database should implement ways to find out who made what change and when and what the old data was. You'll thank me the first time someone malicious changes the data or someone deletes all the records in a table accidentally.

真正想想如何在设计时查询数据。它可以在设计上产生巨大的差异。

Really think through how the data will be queried when designing. It can make a huge difference in the design.

不要在一个字段中存储多个信息。将逗号分隔的列表放在一个字段中而不是添加相关的表格看起来很酷，但这是一个很糟糕的主意。

Do not store more than one piece of information in a field. It might look cool to put a comma delimited list into one field rather than add a related table but it is a really bad idea.

优雅通常是性能的敌人数据库。

Elegance is often the enemy of performance in databases. Pick performance over elegance every time and you won't go wrong.

避免在对象的命名中使用数据库关键字。你的程序员会感谢你。选择一个命名约定，并且始终使用它是一致的。如果一个字段在多个表中，请确保它是相同的名称（异常如果id字段在同一个表中有两个可能的外键使用id字段名称和一个前缀来标识说明Sales_person_id和Customer_person_id之间的差异），相同的数据类型和长度，如果适用于所有这些。

Avoid the use of database keywords in the naming of objects. Your programmers will thank you. Pick a naming convention and be consistent in always using it. If a field is in mulitple tables make sure it is the same name (exception if an id field has two possible foreign keys in the same table use the id field name and a prefix to identify the differnce between say Sales_person_id and Customer_person_id), same datatype and length, if applicable in all of them. Fix misspellings right away, you really don't want to spend the next ten years remembering that in tablea it is the persnoid instead of personid.

阅读有关数据库重构（搜索引擎）的更多信息，在亚马逊的一些好书），并考虑如何能够在您的设计中这样做。很少有数据库被设计为重构，并且能够这样做对于能够修复由严重设计设计或业务需求变化而导致的数据库问题至关重要。

Read about database refactoring (search on amazon for some good books) and consider how to be able to do this in your design. Few databases are designed to be refactored and being able to do so is critical towards being able to fix database problems that arise from badly thought out designs or changes to business requirements.

在阅读时，请阅读关于性能调整的内容，您将了解到在设计数据库时应避免的大量问题。

While you are reading, read about performance tuning, you'll learn a tremendous amount about what to avoid in designing the database.

我确定还有更多，但这是足以开始。

I'm sure there's more but this is enough to start with.

我想添加一个额外的东西。不要设计数据库，就像数据输入应用程序页面是最关键的一样。数据通常比即使在事务数据库中被编写也经常被查询。真的想想如何容易地将数据从数据库中取回（哦，这就是为什么EAV模型是如此糟糕！），以及设计将对报告有什么影响。这是非常重要的，因为我经常看到，做报告的人不是设计数据库或报告任务的人员在项目中比创建数据条目。数据库不容易重构，在设计数据库时考虑数据的整个生命周期。考虑像存储时间值这样的事情，因为你不能知道两年后订单的数量是多少，因为订单数量乘以产品表中的价格，因为这不是订单时的价格。报告需要这种类型，如果信息，但它经常太晚，以获得它的时间的报告写的时候设计做得很糟糕。当你一次处理一个记录时，工作正常的东西可能是一个灾难，当你需要看看成千上万的记录。不是每个应用程序都要创建一个单独的报告数据库，所以真的想想。

One addtional thing I wanted to add. Do not design your database as if the data entry application page is the most critical thing. Data is often queried more often than it is written even in a transactional database. Really think about how easy it will be to to get data back out of the database (Oh so that's why the EAV model is so bad!) and what effect the design will have on reporting. This is espcially critical as I often see that the people doing the reporting are not the people who design the database or reporting tasks are later in the project than createing the data entry. Databases are not easy to refactor, consider the whole life cycle of the data when designing a database. Think about things like storing moment in time values as you can't find out how much an order was for two years later by multiplying the quantity ordered by the price in the products table as that wasn't the price at the time of the order. Reporting needs this type if information, but it often too late to get it by the time the reports are written when the design is done badly. Stuff that works fine when you are handling one record at a time can be a disaster when you need to look at thousands or millions of records. Not every application is going to create a separate reporting datbase, so really think about this.

这篇关于在为应用程序设计数据库时，需要记住什么一般准则和最佳实践？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！