Relational Database Design Patterns

Question

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

User · Answer

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 use in the future another DBMS? If yes then does not use to special SQL stuff of the current DBMS. Remove logic in your application.

Does 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.
does not use SQL keywords for tables or columns names like "FROM", "BETWEEN", "DELETE", etc

recomendations:

Use NVARCHAR or equivalents for unicode support then you have no problems with codepages.
Give every column a unique name. This make it easer 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 easer to switch to another DBMS.
Does not cast hard on any data type. Another DBMS can not have this data type. FOr example Oracle daes not have a SMALLINT only a number.

I hope this is a good starting point.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[design-patterns] Relational Database Design Patterns?

Examples related to design-patterns

Examples related to database-design

Examples related to rdbms