SQL 数据库设计初学者指南
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/377375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
A beginner's guide to SQL database design
提问by ripper234
Do you know a good source to learn how to design SQL solutions?
您知道学习如何设计 SQL 解决方案的好资源吗？
Beyond the basic language syntax, I'm looking for something to help me understand:
除了基本的语言语法，我正在寻找一些帮助我理解的东西：
- What tables to build and how to link them
- How to design for different scales (small client APP to a huge distributed website)
- How to write effective / efficient / elegant SQL queries
- 要构建哪些表以及如何链接它们
- 如何针对不同规模进行设计（小客户端APP到庞大的分布式网站）
- 如何编写有效/高效/优雅的 SQL 查询
采纳答案by inspite
I started with this book: Relational Database Design Clearly Explained (The Morgan Kaufmann Series in Data Management Systems) (Paperback)by Jan L. Harrington and found it very clear and helpful
我从这本书开始：Jan L. Harrington 的Relational Database Design Clearly Explained（The Morgan Kaufmann Series in Data Management Systems）（平装本），发现它非常清晰和有用
and as you get up to speed this one was good too Database Systems: A Practical Approach to Design, Implementation and Management (International Computer Science Series)(Paperback)
并且当您加快速度时，这也很好数据库系统：设计、实施和管理的实用方法（国际计算机科学系列）（平装本）
I think SQL and database design are different(but complementary) skills.
我认为 SQL 和数据库设计是不同（但互补）的技能。
回答by Julius
I started out with this article
我从这篇文章开始
http://en.tekstenuitleg.net/articles/software/database-design-tutorial/intro.html
http://en.tekstenuitleg.net/articles/software/database-design-tutorial/intro.html
It's pretty concise compared to reading an entire book and it explains the basics of database design (normalization, types of relationships) very well.
与阅读整本书相比，它非常简洁，并且很好地解释了数据库设计的基础知识（规范化、关系类型）。
回答by JeeBee
Experience counts for a lot, but in terms of table design you can learn a lot from how ORMs like Hibernate and Grails operate to see why they do things. In addition:
经验很重要，但在表设计方面，您可以从 Hibernate 和 Grails 等 ORM 的运行方式中学到很多东西，以了解它们为什么会这样做。此外：
Keep different types of data separate - don't store addresses in your order table, link to an address in a separate addresses table, for example.
I personally like having an integer or long surrogate key on each table (that holds data, not those that link different tables together, e,g., m:n relationships) that is the primary key.
I also like having a created and modified timestamp column.
Ensure that every column that you do "where column = val" in any query has an index. Maybe not the most perfect index in the world for the data type, but at least an index.
Set up your foreign keys. Also set up ON DELETE and ON MODIFY rules where relevant, to either cascade or set null, depending on your object structure (so you only need to delete once at the 'head' of your object tree, and all that object's sub-objects get removed automatically).
If you want to modularise your code, you might want to modularise your DB schema - e.g., this is the "customers" area, this is the "orders" area, and this is the "products" area, and use join/link tables between them, even if they're 1:n relations, and maybe duplicate the important information (i.e., duplicate the product name, code, price into your order_details table). Read up on normalisation.
Someone else will recommend exactly the opposite for some or all of the above :p - never one true way to do some things eh!
将不同类型的数据分开 - 例如，不要将地址存储在您的订单表中，链接到单独的地址表中的地址。
我个人喜欢在作为主键的每个表（保存数据，而不是将不同表链接在一起的那些，例如，m:n 关系）上有一个整数或长代理键。
我也喜欢创建和修改时间戳列。
确保您在任何查询中执行“where column = val”的每一列都有一个索引。也许不是世界上最完美的数据类型索引，但至少是一个索引。
设置外键。还根据您的对象结构设置相关的 ON DELETE 和 ON MODIFY 规则，以级联或设置为空（因此您只需要在对象树的“头部”删除一次，并且该对象的所有子对象都会得到自动删除）。
如果你想模块化你的代码，你可能想模块化你的数据库模式 - 例如，这是“客户”区域，这是“订单”区域，这是“产品”区域，并使用连接/链接表它们之间，即使它们是 1:n 关系，并且可能会复制重要信息（即，将产品名称、代码、价格复制到您的 order_details 表中）。阅读规范化。
其他人会为上述部分或全部推荐完全相反的 :p - 从来没有一种真正的方式来做一些事情 eh！
回答by user2287824
回答by Dickon Reed
Head First SQLis a great introduction.
Head First SQL是一个很好的介绍。
回答by cheng81
These are questions which, in my opionion, requires different knowledge from different domains.
在我看来，这些问题需要来自不同领域的不同知识。
- You just can't know in advance "which" tables to build, you have to know the problem you have to solve and design the schema accordingly;
- This is a mix of database design decision and your database vendor custom capabilities (ie. you should check the documentation of your (r)dbms and eventually learn some "tips & tricks" for scaling), also the configuration of your dbms is crucial for scaling (replication, data partitioning and so on);
- again, almost every rdbms comes with a particular "dialect" of the SQL language, so if you want efficient queries you have to learn that particular dialect --btw. much probably write elegant query which are also efficient is a big deal: elegance and efficiency are frequently conflicting goals--
- 您只是无法提前知道要构建“哪些”表，您必须知道您必须解决的问题并相应地设计架构；
- 这是数据库设计决策和您的数据库供应商自定义功能的混合（即，您应该检查您的 (r)dbms 的文档并最终学习一些扩展的“技巧和窍门”），而且您的 dbms 的配置对于扩展（复制、数据分区等）；
- 同样，几乎每个 rdbms 都带有 SQL 语言的特定“方言”，因此，如果您想要高效的查询，则必须学习该特定方言 --btw。很可能编写优雅的查询也很有效：优雅和效率经常是相互冲突的目标--
That said, maybe you want to read some books, personally I've used this bookin my datbase university course (and found a decent one, but I've not read other books in this field, so my advice is to check out for some good books in database design).
也就是说，也许你想读一些书，我个人在我的数据库大学课程中使用过这本书（并找到了一本不错的，但我没有读过这个领域的其他书籍，所以我的建议是查看一些数据库设计方面的好书）。
回答by user29208
It's been a while since I read it (so, I'm not sure how much of it is still relevant), but my recollection is that Joe Celko's SQL for Smarties book provides a lot of info on writing elegant, effective, and efficient queries.
我读它已经有一段时间了（所以，我不确定其中有多少仍然相关），但我记得 Joe Celko 的 SQL for Smarties 一书提供了大量有关编写优雅、有效和高效查询的信息.
