Data modeling glossary
Plain-language definitions of common data modeling terms, from attribute, cardinality and crow's foot notation to normalization, surrogate keys, junction tables, reference data, data contracts and slowly changing dimensions.
Attribute
A fact recorded about an entity, such as a customer's email address. Becomes a column in a physical model.
Cardinality
How many instances of one entity can relate to instances of another: one-to-one (1:1), one-to-many (1:N) or many-to-many (N:N).
Chen notation
An ER diagram notation that draws entities as rectangles, relationships as diamonds and attributes as ovals. Common in textbooks.
Composite key
A primary key made of more than one attribute, such as order_id plus line_number.
Conceptual data model
A high-level model of the main business entities and their relationships, for agreeing on scope. See Conceptual, logical and physical data models.
Crow's foot notation
The most common ER diagram notation, which marks the "many" end of a relationship with a three-pronged symbol. See What is an ER diagram?
Data contract
A formal, machine-readable agreement between the producer and the consumers of a dataset, describing its schema, meaning and quality rules. The Open Data Contract Standard (ODCS) is one format.
Data dictionary
A catalog of every entity and attribute with its type, meaning, rules and examples.
DBML
Database Markup Language, a small text language for describing database schemas, used by dbdiagram.io.
DDL
Data Definition Language: the SQL statements (CREATE TABLE, ALTER TABLE, DROP TABLE) that define a database's structure.
Denormalization
Deliberately duplicating data (for example, in a star schema) to make reads simpler or faster.
Dimensional model
An analytics design of fact tables (measurements, such as sales) surrounded by dimension tables (context, such as customer, product and date). Also called a star schema.
Entity
A kind of thing the business keeps data about, such as Customer or Order. Becomes a table in a physical model.
Entity-relationship diagram (ERD)
A diagram of entities, their attributes and the relationships between them.
Foreign key
An attribute that holds the primary key of another entity, implementing a relationship.
Forward engineering
Generating a database (SQL DDL) from a model.
Hierarchy
A tree of records or entities in levels, such as company ownership or a product taxonomy, often modeled with a relationship from an entity to itself.
Junction table
A table that implements a many-to-many relationship by holding the keys of both sides. Also called an associative, bridge or link table.
Logical data model
A detailed model of entities, attributes, keys and relationships, independent of any database product.
Lookup table
A physical table holding a reference list, such as ref_country.
Natural key
An identifier that has meaning in the business, such as an email address or a tax number.
Normalization
Organizing data so each fact is stored once, typically to third normal form (3NF), to avoid inconsistencies.
Optionality
Whether a relationship is required on a given side: must every order have a customer?
Physical data model
A model of tables, columns, types and constraints for one specific database.
Primary key
The attribute (or attributes) that uniquely identify each instance of an entity.
Reference data
Controlled lists of codes that other data points to, such as countries, currencies and statuses. See Reference data.
Reverse engineering
Building a model from an existing database or SQL script.
Schema
The structure of a database: its tables, columns, types and constraints. Also, a named container of tables in many databases.
Slowly changing dimension (SCD)
A technique for keeping the history of changing attributes in a dimension table, for example with valid-from and valid-to dates (type 2).
Subject area
A group of related entities, such as Sales or Inventory, used to organize a large model.
Surrogate key
A meaningless identifier generated by the system, such as an auto-incrementing number or a UUID.
Third normal form (3NF)
A level of normalization in which every non-key attribute depends on the key, the whole key, and nothing but the key.