Naming Conventions Used in the Schema

[LSST Database]

This document outlines proposed naming conventions for tables and columns in the LSST schema. The conventions are taking into the account how EA generates schema, as well as the standard we have been using so far (Docushare Document-1395 which is now becoming obsolete). As of now it is a draft which will be discussed in the next few weeks.

Here is info about naming conventions adopted by others

  • 2MASS: no official naming conventions. It's all lowercase letters, different terms within a name seperated by '_', and some effort is made to keep names short (i.e. flg instead of flag, etc...). Position is 'ra', 'dec', 'x', 'y', 'z' (standard across tables). No units in names.
  • SDSS: camelcase for the column name, with _u, _g, _r, _i, _z suffixes for the filters

Here is a spreadsheet Download summarizing mismatch between c++ and db schema column names

Naming tables

Table names should use the following naming convention: 

<_><tmpl_|tmp_<period>><prefix><purpose>_<extent>_<component><_postfix>

Leading _ is used to show it is an internal table.

tmpl_:

  • Indicates a template table (table with no data, used to create other tables)

tmp_<period>:

  • Indicates a short-lived table (a table that is periodically purged). <Period> indicates purging frequency, for example a table prefixed with tmp_Visit_ would indicate its contents is purged per visit.

Prefix:

  • All AuxCalibration tables are prefixed with aux_, example: aux_Object
  • All Provenance tables are prefixed with prv_ or prv_cnf_. The former set is used for permanent definitions of provenance elements, for example one row in prv_CCD contains information about one particular CCD, e.g. which Amplifiers are on this CCD. The latter set is used for dynamic configuration information, each row contains a single configuration of a given element.
  • Main Telescope tables contain no prefix.

Purpose:

  • it includes things like Science, Calibration, Bias, Dark, Flat, Fringe, IR.

Extent:

  • FPA, CCD, Amp

Component:

  • Exposure, Object, Source, PSF, WCS, etc.

Postfix:

  • optional. Currently it is used only for internal tables which are used to normalize schema. Example: a prefix Group is used in _Science_FPA_Exposure_Group. That table is used to avoid repeating information in the Science_FPA_Exposure table.

Mapping tables follow its own naming scheme: 

_<table X name>To<table Y name>

Template tables

Naming columns

  • names should start with small letter. Example: imageId
  • Avoid using short, common names like "id", instead, use more descriptive names like imageId, sourceId etc. This helps to simplify writing queries, for example as long as column names are distinct we can use NATURAL JOIN.
  • If a name consists of multiple words, use "camel case" convention: capitalize first character of each word except the first, example sourceId, not sourceid and not sourceID
  • if a column is specific to a filter, put the filter name first, for example uAmplitude, gPeriod
  • put column keeping error for another column one after another, and append Err at the end, for example raErr column should be immediately after ra column
  • for right ascension and declination use ra, and decl. Notice that dec is a reserved word in some DBMSes (short for DECIMAL)
  • if there are more than one per-filter columns, group them together: all columns for one filter together. Put non-filter specific columns before filter-specific ones.
  • try to keep the naming in sync with the corresponding C++ classes. For example LSST will name coordinates (col, row), not (x, y)

Naming constraints

All constraints should have unique names (unique within the database):

Primary key name format: 

	pk_<TableName>_<columnName>
	pk_<TableName>_<columnName1>_<columnName2>   (for composed keys)

Examples: 

	pk_Source_imageId (assuming imageId is the primary key) 
	pk_Source_fovId_amp (assuming primary key on (fovId, amp))

Foreign key name format: 

	fk_<thisTableName>_<referencedTableName>_<referencedColName>

Example: 

	fk_Source_Image_imageId

Non-primary-key index name format: 

	idx_<columnName>

Composed key names built as composed primary key names.

unique constraint name format: 

	uq_<columnName>

Schema Style Convention

Casing

  • Use uppercase for reserved words. Examples: INT, VARCHAR, PRIMARY KEY

Column order

Order of columns within a table: primary key first, then columns corresponding to foreign keys (in alphabetical order)

Where to define constraints

Define all constraints except foreign key constraint at the end of table definition, not in the same line where the column defined. This is how EA generates the schema.

Add foreign keys after tables are created, through "ALTER TABLE". This is required by MS SQL Server.

Example: 

CREATE TABLE Object (
	objectId BIGINT NOT NULL,
	PRIMARY KEY (objectId)
);

CREATE TABLE Alert (
	alertId INTEGER NOT NULL DEFAULT 0,
	ampExposureId BIGINT NOT NULL,
	objectId BIGINT NOT NULL,
	timeGenerated DATETIME NOT NULL,
	PRIMARY KEY (alertId),
	KEY (objectId),
	INDEX idx_Alert_timeGenerated (timeGenerated ASC),
	KEY (ampExposureId)
);

ALTER TABLE Alert ADD CONSTRAINT FK_Alert_Object 
	FOREIGN KEY (objectId) REFERENCES Object (objectId);

Others

NOT NULL

Use NOT NULL for columns that will never have NULL values.

Comments

Tables and columns should have comments. We will load comments with the schema into the database, so this is important. When applicable, expected units should be clearly defined in the comments. In case where there are similar per-filter groups, it is ok to put comments for the first group only. For the example how to work with comments, click here

In MySQL, comment length is limited to 60 characters.

Attachments