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 Conventions | Examples |
|---|---|
| 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:
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 Conventions | Examples |
|---|---|
| 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 Convention | Example |
|---|---|
| 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 Convention | Example |
|---|---|
| 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 Convention | Example |
|---|---|
| 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 Convention | Examples |
|---|---|
| Views should contain the string V_ | F_V_People (a view on a fact table) C_V_Age (a view on a classification table) |