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:
This question is related to
design-patterns
database-design
rdbms
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.
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.
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:
Does not use:
recomendations:
I hope this is a good starting point.
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.
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.
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.
Source: Stackoverflow.com