database 设计数据库时最重要的考虑因素是什么？

What are the most important considerations when designing a database?

提问by Cunners

I would like to know from the experienced programmers what they consider to be the most important considerations when designing a new database.

我想从有经验的程序员那里了解他们认为在设计新数据库时最重要的考虑因素。

采纳答案by MrTelly

First off and most important learn and understand the business domain.

首先，最重要的是学习和了解业务领域。

1) Are you looking at a high transaction rate like a busy web site, or low use like a a small company HR system

1）您是在寻找像繁忙的网站那样的高交易率，还是像小型公司的人力资源系统那样低使用率

2) Is security a big issue - are you handling personal details, or financial data. Or is it just a product catalogue

2) 安全性是否是一个大问题——您是在处理个人详细信息还是财务数据。或者它只是一个产品目录

3) Will your users be doing many updates/inserts or is it mainly read only

3) 你的用户会做很多更新/插入还是主要是只读的

4) How many users, what are the usage patterns (peak load or evenly distributed)

4）有多少用户，使用模式是什么（峰值负载或均匀分布）

5) Do you need 24x7, 16x5 or other uptime, 24x7 is much harder to do as you have no down time for maintenance

5) 您是否需要 24x7、16x5 或其他正常运行时间，24x7 更难做到，因为您没有停机维护时间

6) How big is the DB going to go? If it's really big you'll have to design your tables to take account of that and/or partition

6) 数据库有多大？如果它真的很大，你将不得不设计你的表来考虑那个和/或分区

7) Do you need to look at enterprise cluster with hot fail over, or just normal hosting

7) 您是否需要查看具有热故障转移功能的企业集群，或者只是普通托管

8) How will the DB be adminstered, in most DB projects 95% of the effort is spent developing for the users and their applications, DB admin is forgotten

8) 如何管理数据库，在大多数数据库项目中，95% 的精力都花在为用户及其应用程序开发上，数据库管理员被遗忘了

9) DB Admin, from previous includes backups, changes to other systems, integration to other systems, data loading

9) DB Admin，以前的包括备份、更改其他系统、集成到其他系统、数据加载

10) Actually Data loading and using existing data is another big issue in its own right.

10) 实际上数据加载和使用现有数据本身就是另一个大问题。

That's it for a start

这就是开始

回答by Brandon

The database is secondary to your business process design and should cleanly support your business process in a direct and simple way.You will gain far more benefit from a well-formed, clean, entity model than you will from an index here and there. So once your process is defined, you take it and split it up into "entities" as cleanly as possible with relations that make sense. Once you know your entities, they translate into database tables.

数据库对您的业务流程设计来说是次要的，应该以直接和简单的方式干净地支持您的业务流程。您将从格式良好、干净的实体模型中获得比从各处的索引中获得的好处多得多。因此，一旦您的流程被定义，您就可以使用有意义的关系将其尽可能干净地拆分为“实体”。一旦你知道你的实体，它们就会转换成数据库表。

One of the most important things to do is to not overarchitect.

要做的最重要的事情之一是不要过度架构。

To give you an answer with some teeth, let's take a "vehicle" entity as an example. A vehicle has multiple wheels. You have a critical decision to make knowing that there will be multiple wheels attached to the vehicle. You have 2 choices to make - You can make "wheels" a separate entity, or you can make "number of wheels" an integer field in the "Vehicle" entity.

为了给大家一些牙齿的答案，让我们以“车辆”实体为例。一辆车有多个轮子。您需要做出一个关键的决定，因为您知道车辆上会连接多个轮子。您有 2 个选择 - 您可以将“轮子”设为单独的实体，也可以将“轮子数量”设为“车辆”实体中的整数字段。

If you absolutely knowthat you will need to store lots of changing information about each wheel, then create a "Wheel" entity. You now have a relationship between entities (the car and the wheel).

如果您完全知道您将需要存储有关每个轮子的大量变化信息，那么创建一个“轮子”实体。您现在有了实体（汽车和车轮）之间的关系。

If not, a simple field will do just fine.

如果没有，一个简单的字段就可以了。

Thinking through these critical decisions and making things as simple as possible is by far the most important thing for me when designing a database. It can make the difference between things being really easy and really difficult when you build the next layer(s) of your application.

在设计数据库时，考虑这些关键决策并使事情尽可能简单是迄今为止最重要的事情。当您构建应用程序的下一层时，它可以使事情变得非常简单和非常困难。

回答by Ryan Pedersen

1 - Consistency

1 - 一致性

Over time your database will change and other people will need to work with it. Do yourself and them a favor and make sure that the structures are named in such a way that any reasonable person with basic domain knowledge will be able to anticipate the contents of the table. Take the time to write down (could be a simple as notepad) some basic constructs that you use.

随着时间的推移，您的数据库会发生变化，其他人将需要使用它。帮自己和他们一个忙，并确保以这样的方式命名结构，以便任何具有基本领域知识的理性人都能够预测表格的内容。花点时间写下（可以是一个简单的记事本）你使用的一些基本结构。

Examples:

例子：

• Primary keys all start with IdTableName
• Casing of table names is Pascal
• Foreign keys are all TableNameId
• ext...

• 主键都以 IdTableName 开头
• 表名的大小写是 Pascal
• 外键都是 TableNameId
• 分机...

Whether you choose to use underscores or not (substitute any other conversion for underscores) doesn't really matter at the end of the day as long as you are consistent in the way that you use or don't use them.

您是否选择使用下划线（用任何其他转换代替下划线）在一天结束时并不重要，只要您在使用或不使用它们的方式上保持一致。

Your database is the last line of defense for data integrity. Do all of your data access through stored procedures and enforce the integrity of the data by using check constraints, foreign keys and so on. Type the data correctly, don't use VARCHAR(50) when CHAR(5) is more specific and accurate.

您的数据库是数据完整性的最后一道防线。通过存储过程执行所有数据访问，并通过使用检查约束、外键等来强制执行数据的完整性。正确键入数据，当 CHAR(5) 更具体和准确时，不要使用 VARCHAR(50)。

Someone else mentioned something about keeping it simple. Last but not least don't build something because you "think" you will need it next month. Things change quickly and you will end up doing more maintenance on stuff you "thought" you were going to use rather than things that you are using if you fill your database will stuff that serves no purpose.

其他人提到了一些关于保持简单的事情。最后但并非最不重要的一点是，不要因为您“认为”下个月需要它而构建某些东西。事情变化很快，你最终会对你“认为”你将要使用的东西做更多的维护，而不是你正在使用的东西，如果你填满了你的数据库，这些东西将毫无用处。

回答by S.Lott

Fidelity with the real world entities that the database is supposed to model.

与数据库应该建模的真实世界实体的保真度。

回答by William Holroyd

I'd personally suggest picking up or borrowing a copy of "Database Design for Mere Mortals". Everything you'd ever need to consider in designing a database would be listed in that book, and it's in a very methodical and logical order in which you can build out the database. The Table and Column definitions are tedious, but worth every minute used in the end.

我个人建议拿起或借用“Database Design for Mere Mortals”的副本。您在设计数据库时需要考虑的一切都将在该书中列出，并且您可以按照非常有条理和逻辑的顺序构建数据库。Table 和 Column 定义很乏味，但值得最终使用的每一分钟。

I believe you might be able to read the first chapter if the book via Google Books or via the page preview on Amazon.com.

如果这本书通过 Google Books 或 Amazon.com 上的页面预览，我相信您可能能够阅读第一章。

There are some tidbits you can learn over time or from this site as 'best practices', but nothing beats designing it from the ground up the correct way on the first try.

您可以随着时间的推移或从本网站学习一些“最佳实践”的花絮，但没有什么比在第一次尝试时以正确的方式从头开始设计更好的了。

回答by johnny

Know your data.

了解您的数据。

回答by JoeG

who's going to build and maintain it, where , how and with what. DO you have methods and procedures and processes for doing this or just winging it. Certainly the Business needs drive the data needed which should be captured in an implementation independent ERD. But, you also have to think about who will maintain the data over time. As well as who "owns" the data. Who owns entity and attribute definitions.

谁将构建和维护它，在哪里，如何以及用什么。你有这样做的方法、程序和流程吗？当然，业务需求驱动所需的数据，这些数据应该在独立于实现的 ERD 中捕获。但是，您还必须考虑谁将随着时间的推移维护数据。以及谁“拥有”数据。谁拥有实体和属性定义。

回答by Walter Mitty

The information requirements are the most important part.

信息需求是最重要的部分。

This is another way of saying "determine the purpose of your system", from a response provided by CMS.

这是从 CMS 提供的响应中说“确定系统的目的”的另一种方式。

Conceptual data modeling is just an organized way of collecting and presenting the information requirements. Every value stored and served up by the database is connected to an attribute, and every attribute to a domain. Attributes, in turn, describe either entities or relationships among entities. Subject matter entities and relationships make up the conceptual structure of the "real world" that the data describes. Building an ERD from a conceptual model is easy, although tedious.

概念数据建模只是一种收集和呈现信息需求的有组织的方式。数据库存储和提供的每个值都连接到一个属性，每个属性都连接到一个域。反过来，属性描述实体或实体之间的关系。主题实体和关系构成了数据描述的“现实世界”的概念结构。从概念模型构建 ERD 很容易，但很乏味。

After that, you can pick a DBMS, design the logical database, design the physical database, and build. At each step, the decisions you make are more reversible, due to data independence. Data independence encapsulates design decisions inside the database, except for performance consequences. You have to know your tool, of course.

之后，您可以选择一个 DBMS，设计逻辑数据库，设计物理数据库，然后构建。由于数据独立性，在每一步中，您做出的决定都更加可逆。数据独立性将设计决策封装在数据库内，性能后果除外。当然，您必须了解您的工具。

Having a tool for managing models, and converting them to diagrams and scripts can be very helpful at speeding this process up, and cutting down on errors.

拥有用于管理模型并将其转换为图表和脚本的工具对于加快此过程和减少错误非常有帮助。

But if you have serious errors or omissions in your information requirments, no amount of clever design or implementation is going to make up for that.

但是，如果您的信息要求存在严重错误或遗漏，再多的巧妙设计或实施都无法弥补这一点。

回答by CMS

A basic set of points:

一组基本点：

• Determine the purpose of your system.
• Identify the entities that your system will need.
• Identify which information each entity should provide.
• Identify the exiting relationships between your entities
• What would the user want to know about and do with your data.
• Conceptual and Logical Database Design
• Normalization and ERD
• Identify fields with unique values.
• Select the appropriate data types for your fields.
• Database refactoring.

• 确定系统的用途。
• 确定您的系统将需要的实体。
• 确定每个实体应提供哪些信息。
• 确定实体之间的现有关系
• 用户希望了解您的数据并使用您的数据做什么。
• 概念和逻辑数据库设计
• 规范化和 ERD
• 标识具有唯一值的字段。
• 为您的字段选择适当的数据类型。
• 数据库重构。

回答by cruizer

you also have to understand what the database will be used for. if it's for transactions (OLTP), it should be as normalised as possible, and the goal is for transactions to be completed as quickly as possible. if it's for analysis and/or reporting (OLAP), it should be denormalised in many places where you'll be performing aggregation. design considerations for OLTP databases are not applicable to OLAP databases, and vice-versa.

您还必须了解数据库的用途。如果是针对事务（OLTP），则应尽可能规范化，目标是尽快完成事务。如果用于分析和/或报告 (OLAP)，则应该在您将执行聚合的许多地方对其进行非规范化。OLTP 数据库的设计注意事项不适用于 OLAP 数据库，反之亦然。