Relational model
The most common model, the relational model sorts data into tables, also known as relations, each of which consists of columns and rows. Each column lists an attribute of the entity in question, such as price, zip code, or birth date. Together, the attributes in a relation are called a domain. A particular attribute or combination of attributes is chosen as a primary key that can be referred to in other tables, when itās called a foreign key.
Each row, also called a tuple, includes data about a specific instance of the entity in question, such as a particular employee.
The model also accounts for the types of relationships between those tables, including one-to-one, one-to-many, and many-to-many relationships. Hereās an example:
Ā
Ā
Within the database, tables can be normalized, or brought to comply with normalization rules that make the database flexible, adaptable, and scalable. When normalized, each piece of data is atomic, or broken into the smallest useful pieces.
Relational databases are typically written in Structured Query Language (SQL). The model was introduced by E.F. Codd in 1970.
Hierarchical model
The hierarchical model organizes data into a tree-like structure, where each record has a single parent or root. Sibling records are sorted in a particular order. That order is used as the physical order for storing the database. This model is good for describing many real-world relationships.
Ā
This model was primarily used by IBMās Information Management Systems in the 60s and 70s, but they are rarely seen today due to certain operational inefficiencies.
Network model
The network model builds on the hierarchical model by allowing many-to-many relationships between linked records, implying multiple parent records. Based on mathematical set theory, the model is constructed with sets of related records. Each set consists of one owner or parent record and one or more member or child records. A record can be a member or child in multiple sets, allowing this model to convey complex relationships.
It was most popular in the 70s after it was formally defined by the Conference on Data Systems Languages (CODASYL).
Ā
Ā
Object-oriented database model
This model defines a database as a collection of objects, or reusable software elements, with associated features and methods. There are several kinds of object-oriented databases:
AĀ multimedia databaseĀ incorporates media, such as images, that could not be stored in a relational database.
AĀ hypertext databaseĀ allows any object to link to any other object. Itās useful for organizing lots of disparate data, but itās not ideal for numerical analysis.
The object-oriented database model is the best known post-relational database model, since it incorporates tables, but isnāt limited to tables. Such models are also known as hybrid database models.
Ā
Ā
Object-relational model
This hybrid database model combines the simplicity of the relational model with some of the advanced functionality of the object-oriented database model. In essence, it allows designers to incorporate objects into the familiar table structure.
Languages and call interfaces include SQL3, vendor languages, ODBC, JDBC, and proprietary call interfaces that are extensions of the languages and interfaces used by the relational model.
Entity-relationship model
This model captures the relationships between real-world entities much like the network model, but it isnāt as directly tied to the physical structure of the database. Instead, itās often used for designing a database conceptually.
Here, the people, places, and things about which data points are stored are referred to as entities, each of which has certain attributes that together make up their domain. The cardinality, or relationships between entities, are mapped as well.
Ā
Ā
A common form of the ER diagram is the star schema, in which a central fact table connects to multiple dimensional tables.
Other database models
A variety of other database models have been or are still used today.
Inverted file model
A database built with the inverted file structure is designed to facilitate fast full text searches. In this model, data content is indexed as a series of keys in a lookup table, with the values pointing to the location of the associated files. This structure canĀ provide nearly instantaneous reporting in big data and analytics, for instance.
This model has been used by the ADABAS database management system of Software AG since 1970, and it is still supported today.
Flat model
The flat model is the earliest, simplest data model. It simply lists all the data in a single table, consisting of columns and rows. In order to access or manipulate the data, the computer has to read the entire flat file into memory, which makes this model inefficient for all but the smallest data sets.
Multidimensional model
This is a variation of the relational model designed to facilitate improved analytical processing. While the relational model is optimized for online transaction processing (OLTP), this model is designed for online analytical processing (OLAP).
Each cell in a dimensional database contains data about the dimensions tracked by the database. Visually, itās like a collection of cubes, rather than two-dimensional tables.
Semistructured model
In this model, the structural data usually contained in the database schema is embedded with the data itself. Here the distinction between data and schema is vague at best. This model is useful for describing systems, such as certain Web-based data sources, which we treat as databases but cannot constrain with a schema. Itās also useful for describing interactions between databases that donāt adhere to the same schema.
Context model
This model can incorporate elements from other database models as needed. It cobbles together elements from object-oriented, semistructured, and network models.
Associative model
This model divides all the data points based on whether they describe an entity or an association. In this model, an entity is anything that exists independently, whereas an association is something that only exists in relation to something else.
The associative model structures the data into two sets:
- A set of items, each with a unique identifier, a name, and a type
- A set of links, each with a unique identifier and the unique identifiers of a source, verb, and target. The stored fact has to do with the source, and each of the three identifiers may refer either to a link or an item.
Other, less common database models include:
- Semantic model, which includes information about how the stored data relates to the real world
- XML database, which allows data to be specified and even stored in XML format
- Named graph
- Triplestore
NoSQL database models
In addition to the object database model, other non-SQL models have emerged in contrast to the relational model:
TheĀ graph database model, which is even more flexible than a network model, allowing any node to connect with any other.
TheĀ multivalue model, which breaks from the relational model by allowing attributes to contain a list of data rather than a single data point.
TheĀ document model, which is designed for storing and managing documents or semi-structured data, rather than atomic data.
Databases on the Web
Most websites rely on some kind of database to organize and present data to users. Whenever someone uses the search functions on these sites, their search terms are converted into queries for a database server to process. Typically, middleware connects the web server with the database.
The broad presence of databases allows them to be used in almost any field, from online shopping to micro-targeting a voter segment as part of a political campaign. Various industries have developed their own norms for database design, from air transport to vehicle manufacturing.
