创建数据模型的最佳实践[英] Best practices for creating a data model

本文是小编为大家收集整理的关于创建数据模型的最佳实践的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

对于当前项目,我正在创建一个数据模型.是否有任何来源可以找到一个好的数据模型的"最佳实践"?良好意味着灵活,高效,具有良好的性能,样式,...一些示例问题将是"列的命名","应该标准化哪些数据"或"哪些属性应导出到自己的表格中".来源应该是一本书: - )

推荐答案

我个人认为您应该在开始建模数据库之前阅读有关性能调整的书.正确的设计可以使世界有所不同.如果您不是性能调整专家,则没有资格设计数据库.

这些书是特定于数据库的,这是SQL Server的一本书. http:///www.amazon.com/server-performance.com/server-performance-tuning -distild-experts/dp/1430219025/ref = sr_1_1?s = books&ie = utf8&qid = 1313603282&sr = 1-1

在开始设计之前,您应该阅读的另一本书是关于对抗的.总是很高兴知道您应该避免做什么. http:///www.amazon.com/sql-antipatterns-programming and -pragmatic-Programmers/dp/1934356557/ref = sr_1_1?s = books&ie = utf8&qid = 1313603622&sr = 1-1

不要陷入设计陷阱以保持灵活性.人们以此为一种方法来摆脱正确的设计,并且几乎总是表现出色.如果您的数据库设计中有超过5%取决于灵活性,那么我认为您没有正确建模.我必须使用的所有最糟糕的COTS产品都是为了灵活而设计的.

任何不错的数据库书都会讨论归一化.您还可以在网络上轻松找到该信息.确保实际创建FK/PK关系.

就命名列,选择一个标准并始终如一地坚持下去.一致性比实际标准更重要.不要命名列ID(请参阅SQL Antipatterns书).如果列在几个不同的表中,请使用相同的名称和数据类型.您要想要的是由于数据类型不匹配而不必使用函数进行连接.

始终记住,数据库可以(并且将)在应用程序之外进行更改.数据完整性所需的任何内容都必须在数据库中而不是应用程序代码中.该应用程序被替换后将很长时间.

数据库设计最重要的事情:

  • 彻底定义所需的数据(包括正确的数据类型) 以及数据部分之间的关​​系(包括正确的归一化)
  • 数据完整性
  • 性能
  • 安全
  • 一致性(数据类型,命名标准等)

其他推荐答案

我在数据库系统设计上读到的最好的书是"数据库系统简介".乔·塞尔科(Joe Celko)的智能书籍SQL也值得一读. 假设您正在构建应用程序,而不仅仅是数据库,并且假设您使用的是面向对象的语言,那么Craig Larman应用UML和模式就可以在将数据库映射到对象上进行很好的讨论.

根据我的经验,"可维护"可能是列表中的首要任务.数据库设计中的可维护性意味着许多事情,例如坚持公约 - 我经常推荐 http://justinsomnia.org/2003/04/essential-database-naming-conventions-anderventions-and-style/.归一化是另一个明显的可维护性策略.我经常建议您慷慨地使用列类型 - 如果您发现不同国家/地区的邮政代码比美国更长,则很难更改应用程序.我经常建议使用视图将复杂的数据关系抽象为经验不足的开发人员.

具有可维护性的关键是测试和部署的能力.值得阅读有关连续数据库集成(http://www.codeproject.com/kb/architecture/database_ci.aspx) - 虽然与数据库架构的设计并不严格相关,但它是重要的上下文.

.

至于性能 - 我相信您应该首先设计可维护性,并且只有在您知道有问题的情况下才能设计性能.有时,您事先知道性能将是一个主要问题 - 设计用于Facebook(或堆栈交换)的数据库,设计具有大量数据(Terabytes及以上)或大量用户的数据库.大多数系统不会落入该营地 - 因此,我建议定期使用代表性数据进行定期性能测试,以查找您是否有问题,并且只有在您必须证明的情况下进行调整.许多性能优化是以维护性为代价的 - 否定性.

哦,通常,如果可以的话,请避免触发器和存储程序.不过,那只是我的看法...

其他推荐答案

即使不是一本书,我也建议阅读大型数据库的查询评估技术.它提供了有关查询处理的背景,该处理很大程度上会影响您的模式设计,尤其是对于数据密集型(例如,分析)工作负载.它的动手较少,但我相信每个数据库设计师都应该至少阅读一次: - ).

本文地址:https://www.itbaoku.cn/post/597675.html

问题描述

For a current project I'm creating a data model. Are there any sources where I can find "best practices" for a good data model? Good means flexible, efficient, with good performance, style, ... Some example questions would be "naming of columns", "what data should be normalized", or "which attributes should be exported into an own table". The source should be a book :-)

推荐答案

Personally I think you should read a book on performance tuning before beginning to model a database. The right design can make a world of difference. If you are not expert in performance tuning, you aren't qualified to design a database.

These books are Database specific, here is one for SQl Server. http://www.amazon.com/Server-Performance-Tuning-Distilled-Experts/dp/1430219025/ref=sr_1_1?s=books&ie=UTF8&qid=1313603282&sr=1-1

Another book that you should read before starting to design is about antipatterns. Always good to know what you should avoid doing. http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557/ref=sr_1_1?s=books&ie=UTF8&qid=1313603622&sr=1-1

Do not get stuck in the trap of designing for flexibility. People use that as a way to get out of doing the work to design correctly and flexible databases almost always perform badly. If more than 5% of your database design depends on flexibility, you haven't modeled correctly in my opinion. All the worst COTS products I've had to work with were designed for flexibility first.

Any decent database book will discuss normalization. You can also find that information easily on the web. Be sure to actually create FK/PK relationships.

As far as naming columns, pick a standard and stick with it consistently. Consistency is more important than the actual standard. Don't name columns ID (see SQL antipatterns book). Use the same name and datatypes if columns are going to be in several different tables. What you are going for is to not have to use functions to do joins because of datatype mismatches.

Always remember that databases can (and will) be changed outside the application. Anything that is needed for data integrity must be in the database not the application code. The data will be there long after the application has been replaced.

The most important things for database design:

  • Thorough definition of the data needed (including correct datatypes) and the relationships between pieces of data (including correct normalization)
  • data integrity
  • performance
  • security
  • consistency (of datatypes, naming standards etc.)

其他推荐答案

The best book I've read on the design of database systems was "An Introduction to Database Systems". Joe Celko's SQL for Smarties books are also worth reading. Assuming you're building an application and not just a database, and assuming you're using an Object Oriented language, Applying UML and Patterns by Craig Larman has a good discussion on mapping databases to objects.

In terms of defining "good", in my experience "maintainable" is probably top of the list. Maintainability in database design means many things, such as sticking to conventions - I often recommend http://justinsomnia.org/2003/04/essential-database-naming-conventions-and-style/. Normalization is another obvious maintainability strategy. I often recommend being generous with column types - it's hard to change an application if you find out that postal codes in different countries are longer than in the US. I often recommend using views to abstract complex data relations away for less experienced developers.

A key thing with maintainability is the ability to test and deploy. It's worth reading up about Continuous Database Integration (http://www.codeproject.com/KB/architecture/Database_CI.aspx) - whilst not strictly associated with the design of the database schema, it's important context.

As for performance - I believe you should design for maintainability first, and only design for performance if you know you have a problem. Sometimes, you know in advance that performance will be a major problem - designing a database for Facebook (or Stack Exchange), designing a database with huge amounts of data (terabytes and up), or huge numbers of users. Most systems don't fall into that camp - so I recommend regular performance tests, with representative data, to find if you have a problem, and only tune when you can prove you have to. Many performance optimizations are at the expense of maintainability - denormalization, for instance.

Oh, and in general, avoid triggers and stored procedures if you can. That's just my opinion, though...

其他推荐答案

Even though it is not a book I recommend to read Query evaluation techniques for large databases. It gives a background on query processing which largely influences your schema design, especially for data intensive (e.g., analytical) workloads. It is less hands-on but I believe every database designer should read it at least once :-).