Lightweight SQL style guide from Simon Holywell
Lightweight SQL style guide Overview SQL style guide by Simon Holywell is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at https://www.sqlstyle.guide/ General Do Use consistent and descriptive identifiers and names. Make judicious use of white space and indentation to make code easier to read. Store [ISO 8601][iso-8601] compliant time and date information (YYYY-MM-DDTHH:MM:SS.SSSSS). Try to only use standard SQL functions instead of vendor-specific functions for reasons of portability. Keep code succinct and devoid of redundant SQL—such as unnecessary quoting or parentheses or WHERE clauses that can otherwise be derived. Include comments in SQL code where necessary. Use the C style opening /* and closing */ where possible otherwise precede comments with -- and finish them with a new line. Avoid camelCase—it is difficult to scan quickly. Descriptive prefixes or Hungarian notation such as sp_ or tbl. Plurals—use the more natural collective term where possible instead. For example staff instead of employees or people instead of individuals. Quoted identifiers—if you must use them then stick to SQL-92 double quotes for portability (you may need to configure your SQL server to support this depending on vendor). Object-oriented design principles should not be applied to SQL or database structures. Naming conventions General Ensure the name is unique and does not exist as a Postgresql keyword. Keep the length to a maximum of 30 bytes—in practice this is 30 characters unless you are using a multi-byte character set. Names must begin with a letter and may not end with an underscore. Only use letters, numbers and underscores in names. Avoid the use of multiple consecutive underscores—these can be hard to read. Use underscores where you would naturally include a space in the name (first name becomes first_name). Avoid abbreviations and if you have to use them make sure they are commonly understood. Tables Use a collective name or, less ideally, a plural form. For example (in order of preference) staff and employees. Do not prefix with tbl or any other such descriptive prefix or Hungarian notation. Never give a table the same name as one of its columns and vice versa. Avoid, where possible, concatenating two table names together to create the name of a relationship table. Rather than cars_mechanics prefer services. Columns Always use the singular name. Where possible avoid simply using id as the primary identifier for the table. Do not add a column with the same name as its table and vice versa. Always use lowercase except where it may make sense not to such as proper nouns. Aliasing or correlations Should relate in some way to the object or expression they are aliasing. As a rule of thumb the correlation name should be the first letter of each word in the object’s name. If there is already a correlation with the same name then append a number. Always include the AS keyword—makes it easier to read as it is explicit. For computed data (SUM() or AVG()) use the name you would give it were it a column defined in the schema. Uniform suffixes The following suffixes have a universal meaning ensuring the columns can be read and understood easily from SQL code. Use the correct suffix where appropriate. ...