SQL数据库设计入门指南

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:

除了基本的语言语法之外，我还在寻找一些能帮助我理解的东西:

1. What tables to build and how to link them
2. 要构建什么表以及如何链接它们
3. How to design for different scales (small client APP to a huge distributed website)
4. 如何针对不同的规模进行设计(大型分布式网站的小客户端应用)
5. How to write effective / efficient / elegant SQL queries
6. 如何编写有效/高效/优雅的SQL查询

7 个解决方案

#1

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的《关系数据库设计》(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和数据库设计是不同的(但是互补的)技能。

#2

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.

与阅读一整本书相比，这本书非常简洁，它很好地解释了数据库设计的基础(规范化、关系类型)。

#3

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的操作方式中学到很多东西，看看它们为什么要这么做。此外:

1. 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.

   保持不同类型的数据独立——不要在您的订单表中存储地址，例如链接到一个单独的地址表中的地址。

2. 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.

   我个人喜欢在每个表上都有一个整数或长代理键(保存数据，而不是连接不同表的键，e,g)。m:n种关系)这是主要的关键。

3. I also like having a created and modified timestamp column.

   我还喜欢创建和修改的时间戳列。

4. 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.

   确保在任何查询中执行“where column = val”的每个列都有索引。也许不是世界上最完美的数据类型索引，但至少是一个索引。

5. 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).

   设置你的外键。还可以根据对象结构设置DELETE和MODIFY相关规则，根据对象结构设置cascade或设置null(因此只需在对象树的'head'处删除一次，对象的所有子对象都会自动删除)。

6. 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.

   如果你想模块化代码,你可能想要模块化DB模式——举例来说,这是“顾客”区域,这是“订单”的领域,这是“产品”的领域,并利用它们之间的连接/链接表,即使他们1:n的关系,也许复制(即重要信息。，将产品名称、代码、价格复制到order_details表中)。读正常化。

7. Someone else will recommend exactly the opposite for some or all of the above :p - never one true way to do some things eh!

   有些人可能会对以上的建议完全相反:p——从来没有一种真正的方式去做某事，嗯!

#4

I really liked this article.. http://www.codeproject.com/Articles/359654/important-database-designing-rules-which-I-fo

我很喜欢这篇文章。http://www.codeproject.com/Articles/359654/important-database-designing-rules-which-I-fo

#5

Head First SQL is a great introduction.

Head First SQL是一个很好的介绍。

#6

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的Smarties book的SQL提供了很多关于编写优雅、有效和高效的查询的信息。

#7

These are questions which, in my opionion, requires different knowledge from different domains.

在我看来，这些问题需要不同领域的不同知识。

1. 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;
2. 你不可能事先知道要构建哪个表，你必须知道要解决的问题，并相应地设计方案;
3. 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);
4. 这是数据库设计决策和您的数据库供应商自定义功能(即。您应该检查您的(r)dbms的文档，并最终了解一些用于伸缩的“技巧和技巧”)，而且您的dbms的配置对于伸缩(复制、数据分区等)是至关重要的;
5. 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--
6. 同样，几乎所有rdbms都带有SQL语言的特定“方言”，所以如果您想要高效的查询，您必须学习这种特定的方言——顺便说一句。很可能写一个同样有效的优雅查询是很重要的:优雅和效率经常是相互冲突的目标—

That said, maybe you want to read some books, personally I've used this book in 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).

也就是说，也许你想读一些书，我个人在我的datbase大学课程中使用过这本书(找到了一本不错的，但是我没有读过这个领域的其他书，所以我的建议是去看看数据库设计方面的好书)。