Lookup tables
I believe this to be where a table refers to a second table, where many rows/records in the first table share identical fields/column data. In the simpler cases, this table may be replaced by an ENUM type on the original table.
Example, where many users can have the same job title, the "Job Title" table is identified as a lookup table and the "Users" table is identified as the data table (see below):
Users >- Job Title
Transactional / Junction / Many-to-Many tables
I believe this to be where a many-to-many relationship occurs between two tables, and this transaction/junction table resolves this relationship.
Example, where many developers may be working on many projects, the "developer_project" table is identified as the transaction/junction table, and the "Developer"/"Project" tables are indentified as data tables (see below).
Developer -< developer_project >- Project
Data/Entity table
I am pretty much making this one up, but where a table is able to exist on its own, irrespective of other tables I consider this to be primarily a data storage table as opposed to a structural table. It may use lookup tables as part of the normalisation/optimisation process, but this is incidental.
I have seen the term "weak" entity floating around and it may/may not apply here. I need to do further reading to be sure.
Example, "staff" and "building" may exist independently of any other tables whereas transactional or relational tables/entities may not:
Staff
Building
Self-Referencing Table
A self-referencing table has a foreign key to itself, creating a hierarchical structure.
create table employee (
id serial primary key,
name text not null,
supervised_by int null references employee(id)
);
Closure Table
History Table
Subtype/Supertypes
Represents concrete entities and their abstraction. For example, it is wise to abstract an Individual and an Organization into a Party type, so that you can easily add either as a customer on a sales order line item.
Single Table Inheritance:
create table party (
id serial primary key,
party_type int not null references party_type(id),
organization_name text null,
individual_first_name text null,
individual_last_name text null
);
Class Table Inheritance:
create table party (
id serial primary key,
party_type int not null
);
create table organization (
id int primary key references party(id),
name text not null
);
create table individual (
id int primary key references party(id),
first_name text not null,
last_name text not null
);
Fact Table
Represents a process or event to be analyzed, and usually contains (unenforced) keys to dimension tables, as well as some numeric measures.
create table sales_item_fact (
order_id bigint not null,
order_line_item bigint not null,
calendar_key int not null,
customer_key bigint not null,
quantity_sold int not null,
unit_price numeric(19,4) not null
);
Dimension Table
Describes a dimension of a fact table, often with hierarchical attributes. For example you can analyze (break apart) dates into their component pieces to make querying easier.
create table calendar (
id int primary key,
date_iso date unique,
year int,
quarter_of_year_int,
month_of_year int,
...
);
Aggregate Table
