Database pattern

Relational database patterns are patterns that are used in the design of relational databases .

Basic table types

Reference table: A reference table is a table that remains relatively constant over time and has relatively few columns. Key-value reference tables with only two columns are often found. Strings should be used as keys to avoid joins .
Master table: A master table is a table that stores the properties of an object (person, address, etc.) in separate columns. Small master tables should have a unique character string ; In the case of large master tables and master tables whose content changes frequently, an integer should be created as the key.
Transaction table: A transaction table is a table that stores interactions or events between master tables. For example, a list of items that a customer has placed in a shopping cart. Automatically generated integers should be used as the key.
Cross-reference table: A cross-reference table is a table in which the relationships between master tables are stored. In cross-reference tables, many-to-many relationships are shown in several rows. A combination of several columns should be selected as the key.

Extended table types

Limited transaction: A limited transaction is a restriction on a table that defines which transactions are permitted and when. This pattern can be used to reduce the corresponding checks on the application side and to increase the security of the database against incorrectly implemented applications.
Ephemeral primary key: are used when a property of an object offers itself as a primary key (e.g. a customer number ), but this may change. In this case, the corresponding property can be used as a primary key, but changes must be logged in a history table in order to be able to guarantee a subsequent assignment.

Foreign key pattern

Foreign key limitation: indicates when deleting an entry (row) from a table does not delete the entries linked to the entry (in another table). The foreign key limitation is therefore the opposite of the foreign key cascade .; In SQL , a foreign key limit is triggered with the command . This behavior is the default behavior for most database implementations when only the command is given.DELETE RESTRICTDELETE
Foreign key cascade: A foreign key cascade is the opposite of the foreign key limit . When you delete an entry, the entries linked to the entry are also deleted.; In SQL, a foreign key limit is triggered with the command .DELETE CASCADE
Cross-reference validation: Cross-reference validation is used when columns in a master table must have a certain relationship with one another. This relation is stored in a separate cross-reference table. The separate cross-reference table increases the resource consumption of the database, but it is necessary to be able to check the validity of the data.

Security pattern

Read-only lookup table: A read-only lookup table is a table that defines an assignment between two tables, the content of which can be queried in general, but can only be edited by certain roles or groups. One example is the linking of certain products with a discount .

Denormalization pattern

Denormalization patterns allow a database to be denormalized for the purpose of improving runtime behavior.

Fetching: With fetching, data is copied from one table to another (temporary) table (e.g. a transaction table ). Make sure that a change in the source table is not automatically transferred to the target table.
Anticipated aggregation: Main article : Anticipated aggregation; With the anticipated aggregation , values from various sources are calculated in advance in the course of a (long-running) batch processing and temporarily stored in another table. The values are not recalculated for each query, but only in the course of the next batch processing. The advantage is that access is significantly faster and the resources of the database are conserved. The disadvantage is that recently made changes are not taken into account in the calculation.
extension: The table is expanded when a column in the table is formed from other columns through the calculation. This means that the calculation does not have to be carried out again for each query, but only when the entry changes.

Object-relational behavior patterns

Table per inheritance hierarchy: (English: Single Table Inheritance ) uses a single table for each class to map a class tree in a database.
Table per subclass: (English: Class Table Inheritance ) uses a separate table for each concrete or abstract class to map a class tree in a database.
Table per concrete class: (English: Concrete Table Inheritance ) uses a separate table for each concrete class to map a class tree in a database.

Table per inheritance hierarchy
Table per subclass
Table per concrete class

Distribution pattern

The distribution patterns essentially distinguish between no distribution , replication and fragmentation ( sharding ):

Replication takes the same parts of the data and copies them to multiple servers to ensure greater reliability.
The fragmentation distributes different parts of the data and distributes them over several servers in order to guarantee a better load distribution.

Replication can be combined with fragmentation. In addition, when it comes to replication, a distinction is made between master / slave replication and peer-to-peer replication .

Single server "distribution"

Distribution through fragmentation

Distribution with master / slave replication

Distribution with peer-to-peer replication

Single server

The simplest distribution pattern is no distribution. The database runs entirely on a single server that handles all read and write access. The advantage of this pattern is that the server is easy to maintain. Updates, data backups, repairs, upgrades etc. can be handled centrally with this pattern.

In addition, software developers do not have to implement complex logic in order to deal with problems with consistency , availability or partitioning ( see also: CAP theorem ).

This variant is also particularly suitable for graph databases , since latencies due to data access via the network are avoided.

Fragmentation

With fragmentation ( sharding ), different databases or parts of the database that are independent of one another are distributed to different servers, the shards .

This results in a better load distribution. In addition, if the server fails, not all applications fail, only those that have to access or write the respective data.

Since the read and write accesses for specific data are processed by the respective shard alone, there is no data inconsistency.

Federation

Federation is a special case of fragmentation in which a central server, called federation root , automatically determines the distribution of the individual shards.

Master / slave replication

With master / slave replication, a central server, the master , takes over all write access to the database. The changes are then transmitted to the other servers, the slaves . If the master server should fail, a slave can take over the role of master.

Since it takes a while for the changes to be adopted by the slave servers, data inconsistencies can occur for a short time.

All servers allow read access, which results in a load distribution for read access. However, since write accesses are processed centrally, the master represents a bottleneck.

Peer-to-peer replication

With peer-to-peer replication, all servers are connected via a peer-to-peer network. Each server handles both write and read access. Write access is synchronized with all servers.

However, since it takes some time before the write accesses are synchronized, data inconsistencies can occur with this model. This effect is particularly noticeable when the network connection between two locations fails.

The advantage of this configuration is that it is particularly fail-safe. The loss of individual peers does not result in data loss. In addition, this model can easily be scaled horizontally, as further (inexpensive) computers can simply be added in the event of bottlenecks.

Peer-to-peer replication is particularly complex in terms of software development and maintenance (updates, backups, etc.) and therefore requires good planning on the part of the operator.

Fragmentation with master-slave replication

Fragmentation and master-slave replication can also be combined. A master is determined for each data type and replicated on several other servers, which serve as slaves for the master. A server can assume the role of master for one data type and the role of slave for other data types at the same time.

Fragmentation with Peer-to-Peer Replication

The last option is the fragmentation of a peer-to-peer network. Several servers are grouped together to take care of a certain type of data as a peer-to-peer network. Each server can be part of several peer-to-peer networks and thus handle different types of data.

More patterns

Resolution pattern: The resolution pattern is used when a value can come from different sources or can be calculated and it has to be decided which source is to be selected or which calculation model is to be used.
History table: A history table is a table that records changes. With this table, changes can be traced and the original status of the monitored table can be restored. An example of a history table is the "version history" of Wikipedia , in which changes are saved in the form of Diff elements.; see also: version management
Dependency sequencing: In the case of dependency sequencing, a series of commands must be processed in a sequence. Since some instructions may be dependent on the result of other instructions, must have the correct order by means of a directed graph analysis : (English directed analytic graph are calculated and displayed in a separate table).
Secure password reset

Anti-pattern

Reverse foreign key

A reverse foreign key ( English reverse foreign key ) is formed when a specific entry of a table is to prevent a particular entry in another table. At first glance, a reverse foreign key often looks like a primary key .

literature

Scott J. Ambler, Pramodkumar J. Sadalage: Refactoring Databases: Evolutionary Database Design . Prentice Hall , Addison-Wesley , 2011, ISBN 978-0-321-77451-4 , pp. 384 (English).
Scott J. Ambler: Agile Database Techniques . John Wiley & Sons , 2003, ISBN 978-0-471-20283-7 , pp. 480 (English).
Len Silverston: The Data Model Resource Book: Volume 1: A Library of Universal Data Models for All Enterprises . John Wiley & Sons, 2001, ISBN 978-0-471-38023-8 , pp. 560 (English).
Len Silverston: The Data Model Resource Book: Volume 2: A Library of Universal Data Models by Industry Types . John Wiley & Sons, 2001, ISBN 978-0-471-35348-5 , pp. 576 (English).
Len Silverston, Paul Agnew: The Data Model Resource Book: Volume 3: Universal Patterns for Data Modeling . John Wiley & Sons, 2009, ISBN 978-0-470-17845-4 , pp. 648 (English).

Web links

Shivprasad Koirala: 11 important database designing rules which I follow. In: The Code Project . September 7, 2012, accessed March 7, 2013 .

Individual evidence

↑ ^a ^b ^c ^d Database Skills: A Sane Approach To Choosing Primary Keys. In: The Database Programmer. January 14, 2008, accessed March 6, 2013 .
↑ Table Design Pattern: Limited Transaction. In: The Database Programmer. February 27, 2008, accessed March 6, 2013 .
^ The Primary Key That Wasn't. In: The Database Programmer. February 24, 2008, accessed March 6, 2013 .
↑ ^a ^b Different Foreign Keys for Different Tables. In: The Database Programmer. July 27, 2008, accessed March 6, 2013 .
↑ Table Design Patterns: Cross-Reference Validation. In: The Database Programmer. January 20, 2008, accessed March 6, 2013 .
^ Introducing Database Security. In: The Database Programmer. May 11, 2008, accessed March 7, 2013 .
^ ^A ^b ^c Martin Fowler: Patterns of Enterprise Application Architecture . Addison-Wesley-Longman, Amsterdam 2002, ISBN 0-321-12742-0 .
↑ ^a ^b ^c ^d ^e ^f Pramodkumar J. Sadalage, Martin Fowler : NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence . Addison-Wesley, Amsterdam 2012, ISBN 978-0-321-82662-6 (English).
↑ Advanced Table Design: Resolutions. In: The Database Programmer. April 20, 2008, accessed March 7, 2013 .
^ History Tables. In: The Database Programmer. July 20, 2008, accessed March 7, 2013 .
↑ Advanced Algorithm: Sequencing Dependencies. In: The Database Programmer. August 25, 2008, accessed March 7, 2013 .
↑ Advanced Table Design: Secure Password Resets. In: The Database Programmer. November 7, 2008, accessed March 7, 2013 .
↑ False Patterns Such as The Reverse Foreign Key. In: The Database Programmer. February 3, 2008, accessed March 7, 2013 .

[keys-1] Database Skills: A Sane Approach To Choosing Primary Keys. In: The Database Programmer. January 14, 2008, accessed March 6, 2013 .

[2] Table Design Pattern: Limited Transaction. In: The Database Programmer. February 27, 2008, accessed March 6, 2013 .

[3] The Primary Key That Wasn't. In: The Database Programmer. February 24, 2008, accessed March 6, 2013 .

[fkey-4] Different Foreign Keys for Different Tables. In: The Database Programmer. July 27, 2008, accessed March 6, 2013 .

[5] Table Design Patterns: Cross-Reference Validation. In: The Database Programmer. January 20, 2008, accessed March 6, 2013 .

[6] Introducing Database Security. In: The Database Programmer. May 11, 2008, accessed March 7, 2013 .

[PoEAA-7] A ^b ^c Martin Fowler: Patterns of Enterprise Application Architecture . Addison-Wesley-Longman, Amsterdam 2002, ISBN 0-321-12742-0 .

[NoSQL-8] ↑ ^a ^b ^c ^d ^e ^f Pramodkumar J. Sadalage, Martin Fowler : NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence . Addison-Wesley, Amsterdam 2012, ISBN 978-0-321-82662-6 (English).

[9] Advanced Table Design: Resolutions. In: The Database Programmer. April 20, 2008, accessed March 7, 2013 .

[10] History Tables. In: The Database Programmer. July 20, 2008, accessed March 7, 2013 .

[11] Advanced Algorithm: Sequencing Dependencies. In: The Database Programmer. August 25, 2008, accessed March 7, 2013 .

[12] Advanced Table Design: Secure Password Resets. In: The Database Programmer. November 7, 2008, accessed March 7, 2013 .

[13] False Patterns Such as The Reverse Foreign Key. In: The Database Programmer. February 3, 2008, accessed March 7, 2013 .