Keywords: DataAccWG
Hi All,
I created in Docushare a document summarizing the SQL coding
standards. It captures what what discussed in this email thread
and at the LSST Database telecon. Location:
Data Management --> System Engineering --> Environment and Tools
Direct link:
https://www.lsstcorp.org/docushare/dsweb/Get/Document-1395/SQLCodingStandards.doc
Feel free to suggest changes or additions to this document at any time
Thanks,
Jacek
Jacek Becla wrote:
Keywords: DataAccWG
Hi,
Now when we have several schemas written/changed by different groups
of people, it is becoming more and more apparent that we need
to start standardizing the way we code in SQL, name variable etc.
Based on the schemas I saw so far and Maria's suggestions,
I tried to write down the first few rules.
==================
Names of tables and columns should not include "_" or "-",
if multi-word, first words capitalized.
Table name should start with capital letter. Example: AmpImage.
Attribute name should start with small letter. Example: imageId
Avoid using short, common names like "id", instead, use more
descriptive names like "imageId, "sourceId" etc...
Use uppercase for reserved words. Example: INT, VARCHAR, PRIMARY KEY
All constraints should have names, unique within given table.
Example:
CONSTRAINT fk_Filter_id FOREIGN KEY (filter) REFERENCES Filter (id)
Primary key name format: pk_<columnName>.
Example: pk_imageId (assuming imageId is the primary key)
If composed key: pk_<columnName1>_<columnName2>
Example pk_fovId_amp (assuming primary key on (fovId, amp)
Foreign key name format: fk_<referencedTableName>_<referencedColumnName>.
Example fk_Image_imageId.
Composed key names built as composed primary key names.
Non-primary-key index name format: idx_<columnName>
Example: idx_filterId
Composed key names built as composed primary key names.
unique constraint name format: unq_<columnName>
Define all constraints at the end of table definition,
not in the same line where the column defined. Example:
table T (
id INT,
x VARCHAR,
CONSTRAINT pk_id PRIMARY KEY(id)
)
rather than
table T (
id INT PRIMARY KEY,
x VARCHAR
)
Table referenced by a FOREIGN KEY should appear before the table that
references it.
======
I think we will not have time to discuss it tomorrow at
the db telecon, so let's make some progress by emails..
Comments? Additions?
Thanks,
Jacek
_______________________________________________
LSST-data mailing list
[email protected]
http://www.lsstmail.org/mailman/listinfo/lsst-data
_______________________________________________
LSST-data mailing list
[email protected]
http://www.lsstmail.org/mailman/listinfo/lsst-data
