database 查找示例数据库设计 - 最佳实践的好地方

Good place to look for example Database Designs - Best practices

提问by Younes

I have been given the task to design a database to store a lot of information for our company. Because the task is rather big and contains multiple modules where users should be able to do stuff, I'm worried about designing a good data model for this. I just don't want to end up with a badly designed database.

我的任务是设计一个数据库来为我们公司存储大量信息。因为任务相当大并且包含多个模块，用户应该能够在其中做一些事情，所以我担心为此设计一个好的数据模型。我只是不想最终得到一个设计糟糕的数据库。

I want to have some decent examples of database structures for contracts / billing / orders etc to combine those in one nice relational database. Are there any resources out there that can help me with some examples regarding this?

我想要一些像样的合同/账单/订单等数据库结构示例，将它们组合到一个很好的关系数据库中。是否有任何资源可以帮助我提供一些关于此的示例？

采纳答案by HLGEM

Before you start read up on normalization until you have no questions about it at all. If you only did this in school, you probably don't know enough about it to design yet.

在您开始阅读规范化之前，直到您对它没有任何疑问为止。如果你只在学校做过这件事，你可能对它的设计还不够了解。

Gather your requirements for each module carefully. You need to know:

仔细收集您对每个模块的要求。你得知道：

Business rules (which are specific to applications and which must be enforced in the database because they must be enforced on all records no matter the source),

业务规则（特定于应用程序，必须在数据库中强制执行，因为无论来源如何，它们都必须对所有记录强制执行），

Are there legal or regulatory concerns (HIPAA for instance or Sarbanes-Oxley requirements) security (does data need to be encrypted?)

是否存在法律或监管问题（例如 HIPAA 或 Sarbanes-Oxley 要求）安全（数据需要加密吗？）

What data do you need to store and why (is this data available anywhere else)

您需要存储哪些数据以及为什么（这些数据在其他地方是否可用）

Which pieces of data will only have one row of data and which will need to have multiple rows?

哪些数据只有一行数据，哪些需要多行？

How do you intend to enforce uniqueness of the the row in each table? Do you have a natural key or do you need a surrogate key (suggest a surrogate key in almost all cases)?

您打算如何强制每个表中行的唯一性？您有自然键还是需要代理键（在几乎所有情况下都建议使用代理键）？

Do you need replication?

你需要复制吗？

Do you need auditing?

你需要审计吗？

How is the data going to be entered into the database? Will it come from the application one record at a time (or even from multiple applications)or will some of it come from bulk inserts from an ETL tool or from another database.

数据将如何输入到数据库中？它是来自应用程序一次一个记录（甚至来自多个应用程序），还是来自 ETL 工具或另一个数据库的批量插入。

Do you need to know who entered the record and when (highly likely this will be necessary in an enterprise system.

您是否需要知道谁输入了记录以及何时输入（这在企业系统中很可能是必要的。

What kind of lookup tables will you need? Data entry is much more accurate when you can use lookup tables and restrict the users to the values.

您需要什么样的查找表？当您可以使用查找表并将用户限制为值时，数据输入会更加准确。

What kind of data validation do you need?

您需要什么样的数据验证？

Roughly how many records will the system have? You need to have an idea to know how big to create your test data.

系统大约有多少条记录？您需要知道创建测试数据有多大。

How are you going to query the data? Will you be using stored procs or an ORM or dynamic queries?

你将如何查询数据？您将使用存储过程还是 ORM 或动态查询？

Some very basic things to remember in your design. Choose the right data type for your data. Do not store dates or numbers you intend to do math on in string fields. Do store numbers that are not candidates for math (part numbers, zip codes, phone numbers, etc) as string data as you may need leading zeros. Do not store more than one piece of information in a field. So no comma-concatenated lists (these indicate the need for a related table) and while you are at it if you find yourself doing something like phone1, phone2, phone 3, stop right away and design a related table. Do use foreign keys for data integrity purposes.

在您的设计中需要记住一些非常基本的事情。为您的数据选择正确的数据类型。不要在字符串字段中存储您打算对其进行数学运算的日期或数字。将不是数学候选的数字（部件号、邮政编码、电话号码等）存储为字符串数据，因为您可能需要前导零。不要在一个字段中存储多于一条信息。所以没有逗号连接的列表（这些表示需要相关表），如果您发现自己在做类似 phone1、phone2、phone 3 之类的事情，请立即停下来设计一个相关表。出于数据完整性目的，请务必使用外键。

All the way through your design consider data integrity. Data that has no integrity is meaningless and useless. Do design for performance, this is critical in database design and is NOT premature optimization. Database do not refactor easily, so it is important to get the most critical parts of the performance equation right the first time. In fact all databases need to be designed for data integrity, performance and security.

在整个设计过程中都要考虑数据完整性。没有完整性的数据是没有意义和无用的。为性能而设计，这在数据库设计中很关键，而不是过早的优化。数据库不容易重构，因此在第一时间正确处理性能方程式中最关键的部分非常重要。事实上，所有数据库都需要针对数据完整性、性能和安全性进行设计。

Do not be afraid to have multiple joins, properly indexed these will perform just fine. Do not try to put everything into an entity value type table. Use these as sparingly as possible. Try to learn to think in terms of handling sets of data, it will help your design. Databases are optimized to do things in sets.

不要害怕有多个连接，正确索引这些将执行得很好。不要试图将所有内容都放入实体值类型表中。尽可能少地使用这些。尝试学习从处理数据集的角度进行思考，这将有助于您的设计。数据库经过优化，可以在集合中执行操作。

There's more but this is enough to start digesting.

还有更多，但这足以开始消化。

回答by APC

Barry Williams has published a library of about six hundred data models for all sorts of applications. Almost certainly it will give you a "starter for ten" for all your subsystems. Access to this library is free so check it out.

巴里·威廉姆斯 (Barry Williams) 已为各种应用程序发布了一个包含大约 600 个数据模型的库。几乎可以肯定，它会为您的所有子系统提供“十人入门”。访问这个库是免费的，所以检查一下。

It sounds like this is a big "enterprise-y" application your organisation wants, and you seem to be a bit of a beginner with databases. If at all possible you should start with a single sub-system - say, Orders - and get that working. Not just the database tables build but some skeleton front-end for it. Once that is good enough add another, related sub-system such as Billing. You don't want to end up with a sprawling monster.

听起来这是您的组织想要的大型“企业级”应用程序，而且您似乎是数据库的初学者。如果可能的话，您应该从一个子系统开始——比如订单——然后让它工作。不仅构建了数据库表，还构建了一些框架前端。一旦足够好，添加另一个相关的子系统，例如计费。你不想以一个庞大的怪物结束。

Also make sure you have a decent data modelling tool. SQL Power Architectis nice enough for a free tool.

还要确保你有一个不错的数据建模工具。 SQL Power Architect对于免费工具来说已经足够好了。

回答by TomTom

The Data Model Resource Book.

数据模型资源手册。

http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237/ref=dp_cp_ob_b_title_0

http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237/ref=dp_cp_ob_b_title_0

HEAVY stuff, but very well through out. 3 volumes all in all...

沉重的东西，但非常好。一共3卷……

Has a lot of very well through out generic structures - but they are NOT easy, as they cover everything ;) Always a good starting point, though.

有很多很好的通用结构 - 但它们并不容易，因为它们涵盖了所有内容;) 不过，始终是一个很好的起点。

回答by James

Try to keep your concerns separate here. Users being able to update the database is more of an "application design" problem. If you get your database design right then it should be a case of developing a nice front end for it.

尽量在此处将您的顾虑分开。用户能够更新数据库更多的是一个“应用程序设计”问题。如果您的数据库设计正确，那么应该为它开发一个不错的前端。

First thing to look at is Normalization. This is the process of eliminating any redundantdata from your tables. This will help keep your database neat, and only store information that is relevant to your needs.

首先要看的是Normalization。这是从表中消除任何冗余数据的过程。这将有助于保持您的数据库整洁，并且只存储与您的需求相关的信息。

回答by Matthieu Napoli

The database should not be the model. It is used to save informations between sessions of work.

数据库不应该是模型。它用于保存工作会话之间的信息。

You should not build your application upon a data model, but upon a good object oriented modelthat follows business logic.

您不应在数据模型上构建应用程序，而应在遵循业务逻辑的良好的面向对象模型上构建应用程序。

Once your object model is done, then think about how you can save and load it, with all the database design that goes with it.

完成对象模型后，请考虑如何保存和加载它，以及与之相关的所有数据库设计。

(but apparently your company just want you to design a database ? not an application ?)

（但显然你的公司只是想让你设计一个数据库？而不是一个应用程序？）