DATABASE CONVENTIONS
--------------------------------------
1. TABLES (RULES FOR TABLES)
---------------------------------------------
1. Rule 1a (Plural Names) - Table names should be plural, for example, "Customers" instead of "Customer"
2. Rule 1b (Prefixes) - Used correctly, table prefixes can help you organize your tables into related groups or distinguish them from other unrelated tables.
For example, for a healthcare application you might give your tables an "Hc" prefix so that all of the tables for that application would appear in alphabetized lists together. Note that even for the prefix, use Pascal Case.
3. Rule 1c (Notation) - For all parts of the table name, including prefixes, use Pascal Case. Using this notation will distinguish your table names from SQL keywords (all capital letters). For example, "SELECT CustomerId_Pk, CustomerName FROM MyAppGroupTable WHERE CustomerName = '%S'" shows the notation for the table name distinguishing it from the SQL keywords used in the query. PascalCase also reduces the need for underscores to visually separate words in names.
4. Rule 1e (Abbreviations) - Avoid using abbreviations if possible. Use "Accounts" instead of "Accts" and "Hours" instead of "Hrs".
5. Rule 1f (Junction a.k.a Intersection Tables) - Junction tables, which handle many to many relationships, should be named by concatenating the names of the tables that have a one to many relationship with the junction table. For example, you might have "Doctors" and "Patients" tables. Since doctors can have many patients and patients can have many doctors (specialists) you need a table to hold the data for those relationships in a junction table. This table should be named DoctorsPatients". Since this convention can result in lengthy table names, abbreviations sometimes may be used at your discretion.
2. COLUMNS - (incl. PRIMARY, FOREIGN, AND COMPOSITE KEYS)
------------------------------------------------------------
1. When naming your columns, keep in mind that they are members of the table, so they do not need the any mention of the table name in the name. The primary key field is typically the only exception to this rule where including the table name is justified so that you can have a more descriptive field name than just "Id". "CustomerId" is acceptable but not required. Just like with naming tables, avoid using abbreviations, acronyms or special characters. All column names should use Pascal Case to distinguish them from SQL keywords (all upper case).
2. Rule 2a (Identity Primary Key Fields) - For fields that are the primary key for a table and uniquely identify each record in the table, the name should simply be “Id“
3. Rule 2b (Foreign Key Fields) - Foreign key fields should have the exact same name as they do in the parent table where the field is the primary key
4. Rule 2e (Data Type Specific Naming) - Boolean fields should be given names like "IsDeleted", "HasPermission", or "IsValid" so that the meaning of the data in the field is not ambiguous. If the field holds date and/or time information, the word "Date" or "Time" should appear somewhere in the field name.
3. STORED PROCEDURES
---------------------
1. Rule 6a (Prefixes or Suffixes) - The way you name your stored procedures depends on how you want to group them within a listing. If you'd like to group them by the type of CRUD operation they perform, then prefix the name with "Create", "Get", "Update" or "Delete". Using this kind of prefix will, for example, group all of your "Create" procedures together since they will all start with the Create prefix, like "CreateProductInfo" or "CreateOrder". If instead, you would like to have your procedures ordered by the table they perform a CRUD operation on, adding "Create, Get, Update, or Delete" as a suffix will do that for you. For example, "ProductInfoCreate" or "OrdersCreate". If your procedure returns a scalar value, or performs an operation like validation, you should not use a CRUD prefix or suffix. Instead use the verb and noun combination. For example, "ValidateLogin"
2. Rule 6c (Bad Prefixes) - Do not prefix your stored procedures with something that will cause the system to think it is a system procedure. For example, in SQL Server, if you start a procedure with "sp_", "xp_" or "dt_" it will cause SQL Server to check the master database for this procedure first, causing a performance hit.
--------------------------------------
1. TABLES (RULES FOR TABLES)
---------------------------------------------
1. Rule 1a (Plural Names) - Table names should be plural, for example, "Customers" instead of "Customer"
2. Rule 1b (Prefixes) - Used correctly, table prefixes can help you organize your tables into related groups or distinguish them from other unrelated tables.
For example, for a healthcare application you might give your tables an "Hc" prefix so that all of the tables for that application would appear in alphabetized lists together. Note that even for the prefix, use Pascal Case.
3. Rule 1c (Notation) - For all parts of the table name, including prefixes, use Pascal Case. Using this notation will distinguish your table names from SQL keywords (all capital letters). For example, "SELECT CustomerId_Pk, CustomerName FROM MyAppGroupTable WHERE CustomerName = '%S'" shows the notation for the table name distinguishing it from the SQL keywords used in the query. PascalCase also reduces the need for underscores to visually separate words in names.
4. Rule 1e (Abbreviations) - Avoid using abbreviations if possible. Use "Accounts" instead of "Accts" and "Hours" instead of "Hrs".
5. Rule 1f (Junction a.k.a Intersection Tables) - Junction tables, which handle many to many relationships, should be named by concatenating the names of the tables that have a one to many relationship with the junction table. For example, you might have "Doctors" and "Patients" tables. Since doctors can have many patients and patients can have many doctors (specialists) you need a table to hold the data for those relationships in a junction table. This table should be named DoctorsPatients". Since this convention can result in lengthy table names, abbreviations sometimes may be used at your discretion.
2. COLUMNS - (incl. PRIMARY, FOREIGN, AND COMPOSITE KEYS)
------------------------------------------------------------
1. When naming your columns, keep in mind that they are members of the table, so they do not need the any mention of the table name in the name. The primary key field is typically the only exception to this rule where including the table name is justified so that you can have a more descriptive field name than just "Id". "CustomerId" is acceptable but not required. Just like with naming tables, avoid using abbreviations, acronyms or special characters. All column names should use Pascal Case to distinguish them from SQL keywords (all upper case).
2. Rule 2a (Identity Primary Key Fields) - For fields that are the primary key for a table and uniquely identify each record in the table, the name should simply be “Id“
3. Rule 2b (Foreign Key Fields) - Foreign key fields should have the exact same name as they do in the parent table where the field is the primary key
4. Rule 2e (Data Type Specific Naming) - Boolean fields should be given names like "IsDeleted", "HasPermission", or "IsValid" so that the meaning of the data in the field is not ambiguous. If the field holds date and/or time information, the word "Date" or "Time" should appear somewhere in the field name.
3. STORED PROCEDURES
---------------------
1. Rule 6a (Prefixes or Suffixes) - The way you name your stored procedures depends on how you want to group them within a listing. If you'd like to group them by the type of CRUD operation they perform, then prefix the name with "Create", "Get", "Update" or "Delete". Using this kind of prefix will, for example, group all of your "Create" procedures together since they will all start with the Create prefix, like "CreateProductInfo" or "CreateOrder". If instead, you would like to have your procedures ordered by the table they perform a CRUD operation on, adding "Create, Get, Update, or Delete" as a suffix will do that for you. For example, "ProductInfoCreate" or "OrdersCreate". If your procedure returns a scalar value, or performs an operation like validation, you should not use a CRUD prefix or suffix. Instead use the verb and noun combination. For example, "ValidateLogin"
2. Rule 6c (Bad Prefixes) - Do not prefix your stored procedures with something that will cause the system to think it is a system procedure. For example, in SQL Server, if you start a procedure with "sp_", "xp_" or "dt_" it will cause SQL Server to check the master database for this procedure first, causing a performance hit.
0 comments:
Post a Comment