There's a book in Martin Fowler's Signature Series called Refactoring Databases. That provides a list of techniques for refactoring databases. I can't say I've heard a list of database patterns so much.
I would also highly recommend David C. Hay's Data Model Patterns and the follow up A Metadata Map which builds on the first and is far more ambitious and intriguing. The Preface alone is enlightening.
Also a great place to look for some pre-canned database models is Len Silverston's Data Model Resource Book Series Volume 1 contains universally applicable data models (employees, accounts, shipping, purchases, etc), Volume 2 contains industry specific data models (accounting, healthcare, etc), Volume 3 provides data model patterns.
Finally, while this book is ostensibly about UML and Object Modelling, Peter Coad's Modeling in Color With UML provides an "archetype" driven process of entity modeling starting from the premise that there are 4 core archetypes of any object/data model
Design patterns are reusable, by definition. They're patterns you detect in other good solutions.
A pattern is not trivially reusable. You can implement your down design following the pattern however.
Relational design patterns include things like:
One-to-Many relationships (master-detail, parent-child) relationships using a foreign key.
Many-to-Many relationships with a bridge table.
Optional one-to-one relationships managed with NULLs in the FK column.
Star-Schema: Dimension and Fact, OLAP design.
Fully normalized OLTP design.
Multiple indexed search columns in a dimension.
"Lookup table" that contains PK, description and code value(s) used by one or more applications. Why have code? I don't know, but when they have to be used, this is a way to manage the codes.
Uni-table. [Some call this an anti-pattern; it's a pattern, sometimes it's bad, sometimes it's good.] This is a table with lots of pre-joined stuff that violates second and third normal form.
Array table. This is a table that violates first normal form by having an array or sequence of values in the columns.
Mixed-use database. This is a database normalized for transaction processing but with lots of extra indexes for reporting and analysis. It's an anti-pattern -- don't do this. People do it anyway, so it's still a pattern.
Most folks who design databases can easily rattle off a half-dozen "It's another one of those"; these are design patterns that they use on a regular basis.
And this doesn't include administrative and operational patterns of use and management.
AskTom is probably the single most helpful resource on best practices on Oracle DBs. (I usually just type "asktom" as the first word of a google query on a particular topic)
I don't think it's really appropriate to speak of design patterns with relational databases. Relational databases are already the application of a "design pattern" to a problem (the problem being "how to represent, store and work with data while maintaining its integrity", and the design being the relational model). Other approches (generally considered obsolete) are the Navigational and Hierarchical models (and I'm nure many others exist).
Having said that, you might consider "Data Warehousing" as a somewhat separate "pattern" or approach in database design. In particular, you might be interested in reading about the Star schema.
After many years of database development I can say there are some no goes and some question that you should answer before you begin:
questions:
Do you want to use in the future another DBMS? If yes then do not use to special SQL stuff of the current DBMS. Remove logic in your application.
Do not use:
white spaces in table names and column names
non ASCII characters in table and column names
binding to a specific lower case or upper case. And never use 2 tables or columns that differ only with lower case and upper case.
do not use SQL keywords for tables or columns names like "FROM", "BETWEEN", "DELETE", etc
recommendations:
Use NVARCHAR or equivalent for Unicode support then you have no problems with codepages.
Give every column a unique name. This make it easier on join to select the column. It is very difficult if every table has a column "ID" or "Name" or "Description". Use XyzID and AbcID.
Use a resource bundle or equals for complex SQL expressions. It make it easier to switch to another DBMS.
Does not cast hard on any data type. Another DBMS can not have this data type. For example Oracle does not have a SMALLINT only a number.
Depends what you mean by a pattern. If you're thinking Person/Company/Transaction/Product and such, then yes - there are a lot of generic database schemas already available.
If you're thinking Factory, Singleton... then no - you don't need any of these as they're too low level for DB programming.
If you're thinking database object naming, then it's under the category of conventions, not design per se.
BTW, S.Lott, one-to-many and many-to-many relationships aren't "patterns". They're the basic building blocks of the relational model.
Your question is a bit vague, but I suppose UPSERT could be considered a design pattern. For languages that don't implement MERGE, a number of alternatives to solve the problem (if a suitable rows exists, UPDATE; else INSERT) exist.
发布评论
评论（6）
Martin Fowler 的签名系列中有一本名为重构数据库的书。 这提供了重构数据库的技术列表。 我不能说我听过这么多数据库模式列表。
我还强烈推荐 David C. Hay 的 数据模型模式 以及后续的 元数据地图，它建立在第一个地图的基础上，并且更加雄心勃勃和有趣。 光是序言就很有启发性。
Len Silverston 的数据模型资源书籍系列 第 1 卷 也是查找预装数据库模型的好地方包含普遍适用的数据模型（员工、帐户、运输、购买等），第 2 卷 包含行业特定数据模型（会计、医疗保健等），第 3 卷提供数据模型模式。
最后，虽然本书表面上是关于 UML 和对象建模的，但 Peter Coad 的使用 UML 进行颜色建模提供了“ “原型”驱动的实体建模过程以任何对象/数据模型都有 4 个核心原型为前提
There's a book in Martin Fowler's Signature Series called Refactoring Databases. That provides a list of techniques for refactoring databases. I can't say I've heard a list of database patterns so much.
I would also highly recommend David C. Hay's Data Model Patterns and the follow up A Metadata Map which builds on the first and is far more ambitious and intriguing. The Preface alone is enlightening.
Also a great place to look for some pre-canned database models is Len Silverston's Data Model Resource Book Series Volume 1 contains universally applicable data models (employees, accounts, shipping, purchases, etc), Volume 2 contains industry specific data models (accounting, healthcare, etc), Volume 3 provides data model patterns.
Finally, while this book is ostensibly about UML and Object Modelling, Peter Coad's Modeling in Color With UML provides an "archetype" driven process of entity modeling starting from the premise that there are 4 core archetypes of any object/data model
设计模式并不是简单的可重用解决方案。
根据定义，设计模式是可重用的。 它们是您在其他优秀解决方案中检测到的模式。
模式并不是可以轻易重用的。 不过，您可以按照该模式实现您的羽绒设计。
关系设计模式包括以下内容：
使用外键的一对多关系（主从、父子）关系。
与桥接表的多对多关系。
使用 FK 列中的 NULL 管理可选的一对一关系。
星型模式：维度和事实，OLAP 设计。
完全标准化的 OLTP 设计。
一个维度中的多个索引搜索列。
“查找表”，包含一个或多个应用程序使用的 PK、描述和代码值。 为什么要有代码？ 我不知道，但是当必须使用它们时，这是一种管理代码的方法。
统一表。 [有些人称之为反模式；有些人称之为反模式； 这是一种模式，有时是坏的，有时是好的。] 这是一个包含大量预连接内容的表，违反了第二范式和第三范式。
数组表。 该表的列中包含数组或值序列，因此违反了第一范式。
混合用途数据库。 这是一个针对事务处理进行标准化的数据库，但具有许多用于报告和分析的额外索引。 这是一种反模式——不要这样做。 无论如何人们都会这么做，所以这仍然是一种模式。
大多数设计数据库的人可以轻松地说出六个“这是另一个”； 这些是他们经常使用的设计模式。
这不包括使用和管理的行政和操作模式。
Design patterns aren't trivially reusable solutions.
Design patterns are reusable, by definition. They're patterns you detect in other good solutions.
A pattern is not trivially reusable. You can implement your down design following the pattern however.
Relational design patterns include things like:
One-to-Many relationships (master-detail, parent-child) relationships using a foreign key.
Many-to-Many relationships with a bridge table.
Optional one-to-one relationships managed with NULLs in the FK column.
Star-Schema: Dimension and Fact, OLAP design.
Fully normalized OLTP design.
Multiple indexed search columns in a dimension.
"Lookup table" that contains PK, description and code value(s) used by one or more applications. Why have code? I don't know, but when they have to be used, this is a way to manage the codes.
Uni-table. [Some call this an anti-pattern; it's a pattern, sometimes it's bad, sometimes it's good.] This is a table with lots of pre-joined stuff that violates second and third normal form.
Array table. This is a table that violates first normal form by having an array or sequence of values in the columns.
Mixed-use database. This is a database normalized for transaction processing but with lots of extra indexes for reporting and analysis. It's an anti-pattern -- don't do this. People do it anyway, so it's still a pattern.
Most folks who design databases can easily rattle off a half-dozen "It's another one of those"; these are design patterns that they use on a regular basis.
And this doesn't include administrative and operational patterns of use and management.
AskTom 可能是有关 Oracle 数据库最佳实践的最有帮助的资源。 （我通常只是输入“asktom”作为关于特定主题的谷歌查询的第一个单词）
我认为谈论关系数据库的设计模式并不真正合适。 关系数据库已经是“设计模式”对问题的应用（问题是“如何在保持数据完整性的同时表示、存储和使用数据”，而设计就是关系模型）。 其他方法（通常被认为已过时）是导航模型和分层模型（我确信还存在许多其他方法）。
话虽如此，您可能会将“数据仓库”视为数据库设计中某种独立的“模式”或方法。 特别是，您可能有兴趣阅读星型架构。
AskTom is probably the single most helpful resource on best practices on Oracle DBs. (I usually just type "asktom" as the first word of a google query on a particular topic)
I don't think it's really appropriate to speak of design patterns with relational databases. Relational databases are already the application of a "design pattern" to a problem (the problem being "how to represent, store and work with data while maintaining its integrity", and the design being the relational model). Other approches (generally considered obsolete) are the Navigational and Hierarchical models (and I'm nure many others exist).
Having said that, you might consider "Data Warehousing" as a somewhat separate "pattern" or approach in database design. In particular, you might be interested in reading about the Star schema.
经过多年的数据库开发，我可以说，在开始之前，您应该回答一些不可行的问题和一些问题：
问题：
不要使用：
建议：
我希望这是一个好的起点。
After many years of database development I can say there are some no goes and some question that you should answer before you begin:
questions:
Do not use:
recommendations:
I hope this is a good starting point.
取决于你所说的模式是什么意思。 如果您正在考虑个人/公司/交易/产品等，那么是的 - 已经有很多通用数据库模式可用。
如果您正在考虑工厂、单例......那么不 - 您不需要任何这些，因为它们对于数据库编程来说太低级了。
如果您正在考虑数据库对象命名，那么它属于约定类别，而不是设计本身。
顺便说一句，S.Lott，一对多和多对多关系不是“模式”。 它们是关系模型的基本构建块。
Depends what you mean by a pattern. If you're thinking Person/Company/Transaction/Product and such, then yes - there are a lot of generic database schemas already available.
If you're thinking Factory, Singleton... then no - you don't need any of these as they're too low level for DB programming.
If you're thinking database object naming, then it's under the category of conventions, not design per se.
BTW, S.Lott, one-to-many and many-to-many relationships aren't "patterns". They're the basic building blocks of the relational model.
您的问题有点模糊，但我想
UPSERT可能是被认为是一种设计模式。 对于未实现 MERGE 的语言，存在多种解决问题的替代方案（如果存在合适的行，则UPDATE；否则INSERT）。Your question is a bit vague, but I suppose
UPSERTcould be considered a design pattern. For languages that don't implementMERGE, a number of alternatives to solve the problem (if a suitable rows exists,UPDATE; elseINSERT) exist.