This is a draft cheat sheet. It is a work in progress and is not finished yet.
General
Always use lower case |
Use consistent and descriptive identifiers and names. |
first_name, address_1, contact_id |
Include comments in SQL code where necessary. Use the C style opening /* and closing */ |
/*this is a comment*/ |
Names must begin with a letter and may not end with an underscore. |
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 |
ie first_name not firstName, contact_id not contactId |
Column Naming Conventions
Always use the singular name |
first_name not firstNames |
Always use lowercase |
Do not add a column with the same name as its table and vice versa |
Always add a identity column to the table and make it a primary key |
order_id int identity(1,1) |
Common Column Names
part_no |
first_name, last_name |
address_1, address_2 |
postal_code |
order_id, cart_id |
Query Syntax
Always use uppercase for the reserved keywords like SELECT and WHERE |
To make the code easier to read it is important that the correct compliment of spacing is used. Do not crowd code or remove natural language spaces. |
Spaces should be used to line up the code so that the root keywords all end on the same character boundary |
Although not exhaustive always include spaces before and after equals (=), after commas (,), surrounding apostrophes (') where not within parentheses or with a trailing comma or semicolon. |
|
|
Table Naming Convertions
Always use the singlar |
st_contact not st_contacts, |
Use underscores where you would naturally include a space |
ex_order_detail not exOrderDetail |
A two or three character table identifier maybe used, if the database has identefiers stick with the naming convention |
Never give a table the same name as one of its columns and vice versa. |
When concatenating two tables, use a table name that appropriately reflects the relationship |
orders to users relationship would involve order, user, and user_order tables |
Table Prefixes
ex_ |
extra - This table contains specific data to the customer of the database is it's functionality is not repeated in other databases |
hst_ |
history - This table is contains historic data that is used for reporting |
ld_ |
load - This table is used with TDI to load data into the system. It's data is temporary and supplied by a customer |
sb_ |
smartbuy - This table contains infomration related to accounts, branches or carts or transaction |
sc_ |
smartcat - This table contains data related to the smartcat catalog |
st_ |
smartsite - This table contains information related to logins or contact details |
tmp_ |
temporary - This table contains temporary data that can be deleted when done |
Some database's may have table prefixes, a two or three character code to identify the tables function in the database.
Stored Procedures
The name must contain a verb. |
Follow the format of usp_Action_BusinessEntity |
usp_SEL_User, usp_INS_User, usp_DEL_User, usp_UPD_User |
Use the prefix USP |
usp_ not sp_ |
|
