296

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:

  • Storing hierarchical data (e.g. single table with type vs multiple tables with 1:1 key and differences...)
  • Storing data with variable structure (e.g. generic columns vs xml vs delimited column...)
  • Denormalize data (how to do it with minimal impact, etc...)
Improve this question
4
  • I'll lay claim to the best Q&A here for hierarchical data storage: stackoverflow.com/questions/4048151/…
    – orangepips
    Commented Jun 22, 2011 at 11:57
  • 1
    According to our on-topic guidance, "Some questions are still off-topic, even if they fit into one of the categories listed above:...Questions asking us to recommend or find a book, tool, software library, tutorial or other off-site resource are off-topic..."
    – Robert Columbia
    Commented May 17, 2018 at 1:01
  • @RobertColumbia the question was on-topic in 2008, when asked...
    – Sklivvz
    Commented May 18, 2018 at 5:27
  • Check out this list of design pattern resources on relational databases and many areas of software engineering github.com/DovAmir/awesome-design-patterns
    – dov.amir
    Commented Jun 9, 2018 at 14:29

6 Answers 6

Reset to default
175

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

Improve this answer
6
  • 1
    The book is titled [Refactoring Databases: Evolutionary Database Design][1] by Scott W. Ambler and Pramod J. Sadalage and is indeed very good. [1]: ambysoft.com/books/refactoringDatabases.html
    – Panos
    Commented Sep 28, 2008 at 12:09
  • 4
    Regarding Ambler book: No, you can't list "inserting a column" or "creating FK constraint" as a pattern for the same reason The Gang of 4 book doesn't list the "for" loop being a pattern.
    – Tegiri Nenashi
    Commented Jul 29, 2011 at 17:53
  • It's not a pattern it's a refactoring. Like extract method, or rename parameter. Refactoring and patterns go hand in hand.
    – Michael Brown
    Commented Jul 30, 2011 at 11:29
  • One to add: "Analysis Patterns" by Fowler. Similar to Hay's stuff
    – Neil McGuigan
    Commented Apr 12, 2012 at 5:08
  • 4
    Len Silverston's Volume 3 is the only one I would consider as "Design Patterns." The first 2 show sample data models which was common in the time frame the books were written. Volume 3 though actually has multiple design patterns for a given problem scenario. E.g., chapter 4 covers hierarchies/aggregations/peer-to-peer scenarios, and then offers multiple designs that address those WITH pros and cons of each.
    – DarrellNorton
    Commented Jun 16, 2013 at 10:59
50

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:

  1. One-to-Many relationships (master-detail, parent-child) relationships using a foreign key.

  2. Many-to-Many relationships with a bridge table.

  3. Optional one-to-one relationships managed with NULLs in the FK column.

  4. Star-Schema: Dimension and Fact, OLAP design.

  5. Fully normalized OLTP design.

  6. Multiple indexed search columns in a dimension.

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

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

  9. Array table. This is a table that violates first normal form by having an array or sequence of values in the columns.

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

Improve this answer
4
  • Some other patterns I've seen are multi-parent child table (ie, like a global notes with a objecttype and objectid that can link to any other table), or a self-referential FK (ie, employee.manager -> employee.id). Also I've used a singleton config table that has many many columns.
    – r00fus
    Commented Jul 17, 2010 at 6:35
  • 1
    Why exactly is a mixed-use database an anti-pattern. What am I meant to do if I want to pull reports from a database?
    – olive
    Commented Sep 28, 2010 at 12:00
  • 3
    @lhnz: You can't pull a lot of large reports from a transactional database design -- locking for reporting will slow down transactions. Complex joins (performed over and over again) are another knock against transaction performance. You can't do both in one database. To do a lot of large reports, you must move the data into a star schema. The star schema pattern is optimized for reporting. And moving the data removes any lock contention.
    – S.Lott
    Commented Sep 28, 2010 at 12:04
  • Would normalising the schema reduce row lock contention if you're making the tables hold more "cohesive" data? My thinking is that if a large table was servicing writes to 2 kinds of data sets but both are in the same row, this would result in unnecessary lock contention.
    – CMCDragonkai
    Commented Jul 2, 2015 at 17:39
6

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.

Improve this answer
4

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.

I hope this is a good starting point.

Improve this answer
2
  • 9
    Although your comments are quite instructive and useful, they are not design patterns. They are best practices. Thanks,
    – Sklivvz
    Commented Sep 28, 2008 at 12:00
  • 9
    I disagree with the recommendation for unique column names. I'd rather say customer.id to disambiguate than to say customerid even where there is nothing to disambiguate.
    – Paul Tomblin
    Commented Sep 28, 2008 at 12:55
2

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.

Improve this answer
1
  • 1
    what about database inheritance like (person, customer, employee) maybe that kind of thing could be considered as design pattern ?
    – Muflix
    Commented Jan 31, 2015 at 22:49
1

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.

Improve this answer
3
  • UPSERT is a command and part of the SQL language. It is not a pattern.
    – Todd R
    Commented Aug 27, 2010 at 13:56
  • UPSERT is a command in some variants of the SQL language - a number of platforms don't have it, or only got it recently.
    – Steve Homer
    Commented Oct 20, 2011 at 16:17
  • @ToddR - I've heard said (slightly cynically) that "patterns" are really nothing more than shortcomings in a language or model, that the user must create work-arounds for. I don't know what UPSERT does, but while it has been added to some SQLs and not others, it is a pattern.
    – Martin F
    Commented Apr 6, 2015 at 17:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.