Terminology for table types

database-designterminology

I am currently unsure of the correct terminology for types of tables that exist, or indeed whether there is a term for the examples I have.

I have included my current understanding of the types that I am aware of further down in this question.

Q: Please provide a list of the different table types, with brief definitions and examples to illustrate them.

Sidenote: The context to this is that I am currently attempting to reduce a database to a bare-bones version (for testing). As part of this I am hoping to identify tables that contain values to be looked up and will not change (eg job titles and department titles) vs the "data" tables which may be emptied with no ill-effect on the remainder of the database (cf, referential integrity in the case of adding a staff member where no departments or job titles are defined).

Best Answer

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
);
  • In data warehousing:

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

Related Question