[design-patterns] Relational Database Design Patterns?

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:

  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.

Examples related to design-patterns

How to implement a simple scenario the OO way Implementing Singleton with an Enum (in Java) What is difference between MVC, MVP & MVVM design pattern in terms of coding c# Best Practices for mapping one object to another REST API Login Pattern When should we use Observer and Observable? How to implement a FSM - Finite State Machine in Java Function in JavaScript that can be called only once Thread Safe C# Singleton Pattern Repository Pattern Step by Step Explanation

Examples related to database-design

What are OLTP and OLAP. What is the difference between them? How to create a new schema/new user in Oracle Database 11g? What are the lengths of Location Coordinates, latitude and longitude? cannot connect to pc-name\SQLEXPRESS SQL ON DELETE CASCADE, Which Way Does the Deletion Occur? What are the best practices for using a GUID as a primary key, specifically regarding performance? "Prevent saving changes that require the table to be re-created" negative effects Difference between scaling horizontally and vertically for databases Using SQL LOADER in Oracle to import CSV file What is cardinality in Databases?

Examples related to rdbms

IN vs ANY operator in PostgreSQL What is the difference between DBMS and RDBMS? NoSql vs Relational database What are database constraints? What are the different types of keys in RDBMS? What does the term "Tuple" Mean in Relational Databases? Drop all tables command Relational Database Design Patterns?