建立高效数据库的方法
我想知道是否可以借鉴经验丰富的数据库设计人员的经验。我在构建数据库方面不太有经验，并且我有一个项目要在规定的时间内（几个月）完成。要求是将其构建为第三范式。
我只是想知道最好的开始方式是什么？我应该继续构建一些可行的东西（尝试尽可能高效），然后返回并重构任何可以改进的部分，或者是否需要遵循一些方法来确保从一开始就达到一定程度的规范化？
I was wondering if I could draw on experienced database designers. I;m not very experienced with building databases and I have a project to complete within set time (couple of months). The requirement is to build it to third normal form.
I was just wondering what the best way to start would be? Should I go ahead and build something that works (trying to be as efficient as possible) as then go back and refactor any parts that can be improved or is there some methodology to follow to ensure certain degrees of normalisation from the start?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不要为了稍后返回并规范化数据库而构建数据库。一方面，数据库不像应用程序代码那样容易重构，另一方面，您已经要求遵循第三范式，因此从一开始就这样做，工作量会少得多，最终会得到更好的产品。在第三范式中没有正确的数据库设计的捷径。在开始设计之前先清楚地了解它是什么。
如果您不熟悉第三范式中的数据库设计，请花几天时间阅读有关数据库设计的内容。然后花一天左右的时间阅读有关数据库性能调优的内容 - 从一开始就设计一个数据库以使其性能良好，这将为您节省大量时间。一旦您知道该做什么和该避免什么，它并不比设计一个性能不佳的数据库花费更长的时间。所以我给你的第一个建议是在开始之前买几本厚厚的书并深入阅读。你花在这件事上的时间将会得到很好的回报，因为你有能力在项目的其余部分做得更好。
Do not build a database with the intent to go back later and normalize it. Databases do not refactor as easily as application code for one thing and for another, you already have a requirement to follow the third normal form, so do so from the start, it will be far less work and in the end a far better product. There is not shortcut to correct database design in the third normal form. Just get to understand clearly what it is before starting to deisgn.
If you are unfamiliar with database dsign in the third normal form, go spend a couple of days doing some reading on database design. THen spend a day or so reading about performance tuning a database - designing a database from the start to perform well will save you a lot of time. It doesn't take longer than designing a poorly performing datbase, once you know what to do and what to avoid. So my first advice to you is to get a couple of big fat books and read in depth before you start. The time you spend doing this will be well repaid in your ability to do things better through the rest of the project.
您肯定会在此数据库层之上编写许多层（DAL、BAL、UI）。较低级别的每个更改都会向上冒泡，并导致比数据库层更多的返工。
我认为您应该完成所有设计，并使数据库达到所需的最佳规范化，以便从一开始就保存数据而不会出现异常。
You will certainly write many layers (DAL, BAL, UI) on top of this database layer. Every change at a lower level will bubble upwards and cause more re-work than just at the database layer.
I think you should do all your design and get the database to the best normalization required/needed to save your data without anomalies at the outset.
与前端编程不同，重构数据库结构一旦使用，不仅是痛苦的，而且是不切实际的。
我强烈建议您先了解后端的结构。好消息是，第三范式通常并不难实现。事实上，一旦你变得更加实际，用这些术语来思考数据几乎就成了你的第二天性。
第三范式本质上表明表中单个记录的每个组成部分对于该记录的键都是唯一的。考虑一下：
tblPerson：
人员ID PK
姓
名
中间名字
出生日期
社会保障号
收入来源
IncomeAmount
上面定义的表不是第三范式，因为收入来源对于表中的每个人来说并不是唯一的，并且事实上在该人的一生中可能会发生变化。相反，我们可以将上述内容（用过于简单的术语）构造如下：
tblPerson
人员ID PK
姓
名
中间名字
出生日期
SSN
tblPersonIncome
PersonIncomeID PK
PersonID FK tblPerson
IncomeSourceID FK tblIncomeSource
收入金额
截至日期
tblIncomeSource
收入来源ID
IncomeSource
请注意，有些人会争论我对上面自动递增 ID 字段的使用 - 虽然这些字段不违反第三范式，但它们也不使用自然键。
如果您的后端从跳转中正确标准化，那么将来添加或扩展您的实体会更容易，并且对您在添加之前创建的任何前端造成的影响要小得多。
不管怎样，在前端和后端进行正确的设计，否则一旦你开始滚动并需要稍后实施结构性改变，痛苦将会非常严重。否则就有点像为你的新房子浇地基，并计划在建造它时改变平面图以满足你以后的需要。
Unlike Front-Side programming, refactoring a database strucure, once in use, is not only painful, but impractical.
I strongly recommend getting the structure of the back-end right up front. THe good news is that third normal form is not generally difficult to acheive. In fact, once you get a little practive, it becomes almost second nature to think of data in those terms.
The Third normal form essentially states that each component of a single record in a table be unique to the key for that record. COnsider:
tblPerson:
PersonID PK
LastName
FirstName
MiddleName
DOB
SSN
IncomeSource
IncomeAmount
The Table as defined above is NOT in thrid normal form, since income source is not unique to each incidence of Person in the table, and in fact is subject to change over the life of that person. Instead, one would structure the above (in overly simplistic terms) as follows:
tblPerson
PersonID PK
LastName
FirstName
MiddleName
DOB
SSN
tblPersonIncome
PersonIncomeID PK
PersonID FK tblPerson
IncomeSourceID FK tblIncomeSource
IncomeAmount
AsOfDate
tblIncomeSource
IncomeSourceID
IncomeSource
Note that some will argue about my use of auto-incrementing ID fields above - While these do not violate the 3rd normal form, they also do not utilize a natural key.
If your back-end is properly normalized from jump, adding or extending your entities is much easier to do in the furure, and will pose a much smaller impact upon any front-end you create prior to the additions.
Anyway, design correctly out front on your back end, or the misery will be intense once you get rolling and need to implement structural changes later. To do otherwise would be a little like pouring the foundation for your new house, and planning to change the floorplan to suit your needs later, while building it.