Relational Database Design Patterns?
Design patterns are usually related to object oriented design.
Are there design patterns for creating and programming relational databases?
Many problems surely must have reusable solutions.
Examples would include patterns for table design, stored procedures, triggers, etc...
Is there an online repository of such patterns, similar to martinfowler.com?
Examples of problems that patterns could solve:
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
Here is a link to a gentleman who has developed several hundred free database schemas.
http://www.databaseanswers.org/data_models/
Perhaps if you have to build a db quickly this will give you a starting point in terms of the tables and relationships in a given schema. Keep in mind you will probably need to modify this starting point. I have found it very useful.
Secondly SQL Server Magazine has an occasional column called "The Data Modeler" which is very educational and often contains complete schemas for a given system.
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 patters 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.
链接地址: http://www.djcxy.com/p/70614.html上一篇: 条件外键约束
下一篇: 关系数据库设计模式？