Skip to main content
Skip table of contents

Database Naming Conventions

This section describes the naming conventions recommended by Space-Time Research when using RDBMS sources to create SuperSTAR databases. While you do not have to following the recommended naming conventions, doing so can make it much easier to use SuperCHANNEL with your data.

General Naming Conventions

Table and column names:

  • Should contain only letters, numbers and underscores (no special characters or spaces).
  • Should use meaningful names: if there is a business term for a field, then that should be used (especially when it is referenced in metadata). 

Fact tables

In a star schema, fact tables contain the raw data. For example, in the sample Retail Banking database there is a Customer fact table; each record in this fact table relates to an individual customer.

Rules and Naming ConventionsExamples
A fact table name should start with F_F_Customer
Columns containing text should be defined as varchar where possible 
All fact tables must have a primary key column. The name of the column should start with PK_

Primary key in Customer table: PK_CustomerID

Primary key in Account table: PK_AccountID

If your database contains multiple fact tables, then you must have foreign keys to link the fact tables together.

Foreign key names should start with FK_ and the name should reference either the table it links to or the primary key column in that table.

Primary key in Customers table: PK_CustomerID

Foreign key in Accounts table: FK_CustomerID

If a fact table has more than one primary key, use numbers in the PK_ prefixes.

PK1_ColumnName

PK2_ColumnName

Classification tables

A classification table (sometimes referred to as a dimension or lookup table) is a table containing a description of the data.

At a minimum, classification tables must contain two columns:

  • A code column. The primary key and unique identifier for this classification type. It is the link between the fact table and the descriptions in the classification table.
  • A name column. The full description of the classification.

Simple Classification Tables

Simple classification tables contain only one level of description.

For example, suppose the fact table contains a Gender column, with each record having one of the following codes: M, F, U. The classification table for this column would contain the descriptions for these codes:

The columns in the classification table do not have to be named Code and Name. However, it is strongly recommended that you follow this simple naming convention as it makes it much easier to work with the data in SuperCHANNEL.

Simple classification tables can also optionally contain a Sort column. This can be used to set the sort order of the classification descriptions in the SuperSTAR clients. See Sort Classification Tables - SuperCHANNEL for more information.

Hierarchical Classification Tables

Hierarchical classification tables contain multiple levels of description.

They must include a foreign key column, in addition to the code and name columns. The foreign key is the link between the classification table and the next level of description.

For example:

There is no limitation on the number of levels that you can have in a hierarchical classification table (although the more levels there are, the slower the cross-tabulation will be).

Naming Conventions for Classification Tables

Naming ConventionsExamples
A classification table name should start with C_C_Geography

Hierarchical classification table names should also start with C_

Where practical, hierarchical classification table names should end with _X (where 'X' is the level inside the hierarchy, the lower level being '0')

The column names should use the naming convention:

 

Staging tables

Staging tables can be used to temporarily create other tables (such as fact or classification tables).

These tables are not used by SuperCHANNEL.

Naming ConventionExample
Staging table names should start with S_S_Account

Multi-response tables

Multi-response tables are commonly used with survey data. They contain information such as survey responses where a respondent can give multiple answers to a particular question.

Naming ConventionExample
Multi-response tables should start with MR_MR_Reason

Registry tables

Registry tables are "internal" tables that SuperCHANNEL can interpret to automatically set links, usages, summations, and so on.

Naming ConventionExample
Registry tables should start with R_R_Classifications

For more information about registry tables, see Registry Tables.

Views

Views are logical tables that are based on other tables or views. They can be used to apply constraints to source data to facilitate the design and content of the target star schema.

For example, a view could be used to restrict the available columns, rename or reorder them, and restrict the data values.

Naming ConventionExamples
Views should contain the string V_

F_V_People (a view on a fact table)

C_V_Age (a view on a classification table)

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.