Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
2  
Don't forget Self-Referencing Table, Closure Table, History Table, Subtype/Supertypes. In data warehousing: Fact Table, Dimension Table, Aggregate Table.. A junction table is also called a Many-to-Many table. –  Neil McGuigan Jun 28 '13 at 16:33
2  
What is your question? This might be a good candidate for community wiki, but it doesn't meet the normal criteria for SO, in that there's really no single question and answer. –  Jon of All Trades Jun 28 '13 at 17:10
    
Apologies Jon, it appears that I removed the actual question out in a last-minute edit before posting. Thank you for giving the nudge to realise this mistake :). Essentially I am asking for a sanity check to ensure my understanding of the terms is correct and to ask for details of additional terms/types that I am missing. I shall edit the question now. –  kwah Jun 28 '13 at 17:14
    
With regards to this "question", while I have attempted to indicate that some research has been done and to be helpful by providing my current understanding, would it be better to condense the question to "what are the different table types" and then place it in an answer to be commented on/edited? I ask this with a hat-tip to Neil's comment - should those terms/details be added to the question as a request for definitions else left as a comment for another answerer to include in their answer? –  kwah Jun 28 '13 at 17:16
    
As a CW, I think that would absolutely be the right approach. Go ahead and move your existing partial answer into an actual answer, and then visitors (like Neil) can add more answers with more tables. –  Jon of All Trades Jun 28 '13 at 18:37

2 Answers 2


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

share|improve this answer
    
Started off using table types from the original question and Credit to Neil McGuigan for some of the table-types: dba.stackexchange.com/questions/45426/… –  kwah Jun 29 '13 at 8:24

Two specialized types of lookup tables exist: Numbers and Dates.


A Numbers table is simply one record for values from, say, zero to 10,000, often with columns providing different formats (spelled-out; with different regional formats; Roman numerals). Such a table can be used when you need a sequence, independently of ROW_NUMBER, ROWNUM, or TOP.

This concept is explored on DBA.SO: Why are numbers tables "invaluable"?.


A Dates table is similar, with one record for each day within the likely domain. Column include formats such as year alone, year and quarter, quarter alone, day of the week, etc. If you include a boolean field to identify business days, you can use such a table to calculate the number of business days between two dates, or in a month, for example. So sales dropped from 2013/05 to 2013/06; did you take into account that the latter has only 20 business days, down from 22? Another useful option is to list both calendar and fiscal quarters.

Obvious variations include month tables. You can join a Dates table to rows 0 through 23 of a Numbers table if you need one record per hour.

There are several blogs about this type of structure:

http://www.techrepublic.com/blog/datacenter/simplify-sql-server-2005-queries-with-a-dates-table/326

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.