Transcript Poor Naming Standards

Poor Naming Standards
Presented by:
Niño R. Ricon
Alain Anuevo
MIT
Presented to Dr. Armando and
to the class of Database Design
A. Database naming conventions
The following types of database objects are discussed here:
1.
2.
3.
4.
5.
6.
7.
Tables
Columns (incl. Primary, Foreign and Composite Keys)
Indexes
Constraints
Views
Stored Procedures
Triggers
ALL DATABASE OBJECTS
•
•
•
•
•
•
•
•
Limit the name to 30 characters
Use only letters or underscores
Try to use underscore characters as little as possible
Use a letter as the first character of the name
Avoid abbreviations
Avoid acronyms
Makes the name readable
Avoid using spaces in names even if the system allows it
1. TABLES
Rule #1: Plural Names - This rule is applicable because
tables are logical collections of one or more entities as
records - just like collection classes are logical collections
of one or more objects.
Example: Customers instead of Customer
UserRoles instead of UserRole
1. TABLES
Rule #2: Prefixes- if used correctly, table prefixes can help
you organize your tables into related groups or
distinguish them from other unrelated tables ELSE they
can cause you to have to type a lot of unnecessary
characters.
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.
1. TABLES
Rule #3: Notation - For all parts of the table name, including
prefixes, use Pascal Case. Using this notation will distinguish
your table names from SQL keywords.
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.
1. TABLES
Rule #4: Special Characters
•
•
•
•
The underscore character has a place in other object
names but, not for tables
Using Pascal Case for your table name allows for the
upper-case letter to denote the first letter of a new word
or name
Do not use numbers in your table names
Do not use spaces in your table names
Example: PurchaseOrder instead of Purchase_Order
SalesOrder instead of [sales order]
SalesInventory instead of sales inventory 1
1. TABLES
Rule #5: Abbreviations- avoid using abbreviations if
possible.
Example: Accounts instead of Accnts
Hours instead of Hrs
1. TABLES
Rule #6: Junction/Intersection Tables - its 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.
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 - 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.
Rule #1: Identify 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“ since,
that's what it is - an identification field.
Example: Customer table with primary key fields
named ID
2. COLUMNS
Rule #2: Foreign Key fields- it should have the exact same
name as they do in the parent table where the field is the
primary key - with one exception - the table name should
be specified.
Example: Customer table where primary key fields
named ID and the foreign key is CustomerID
from the Orders table.
2. COLUMNS
Rule #3: Composite Keys- If you have tables with
composite keys (more than one field makes up the unique
value) then instead of just “Id“ you should use a
descriptor before the “Id“ characters.
Example: ID, ModuleID, CodeID
2. COLUMNS
Rule #4: Prefixes- Do not prefix your fields with "fld_" or
"Col_" as it should be obvious in SQL statements which
items are columns (before or after the FROM clause).
Including a two or three character data type prefix for the
field is optional and not recommended,
Example: "IntCustomerId" for a numeric type or
"VcName" for a varchar type.
2. COLUMNS
Rule #5: Data Type Specific Naming
Example: Boolean data types = “IsDeleted” or
”HasPermission”
Date/Time data types = “RuntimeHours”
or “ScheduledMinutes”
3. INDEXES
Rule #1: Naming Conventions
Indexes Structure:
{TableName}{ColumnsIndexed}{U/N}{C/N}
where:
U/N - Unique or Non-Unique
C/N – Clustered or Non-Clustered
Example:
OrderDetailsOrderIdCustomerIdNN - indicates a nonunique, non-clustered index on the OrderId and
CustomerId columns in the OrderDetails table.
3. INDEXES
Rule #2: Prefixes and Suffixes
Avoid "idx" or "IDX_" before your indexes.
A suffix of "_idx" or "IDX" is not necessary.
4. CONSTRAINTS
Rule #1: Naming Conventions
Syntax:
{constraint type}{table name}_{field name}
Examples:
1. PkProducts_Id - primary key constraint on the Id field of the
products table
2. FkOrders_ProductId - foreign key constraint on the
productId field in the Orders table
3. CkCustomers_AccountRepId - check constraint on the
accountRepId field in the Customers table
4. CONSTRAINTS
Rule #2: Prefixes
Primary Key: Pk
Foreign Key: Fk
Check: Ck
Unique: Un
5. VIEWS
Rule #1: Prefixes
While it is pointless to prefix tables, it can be helpful
for views. Prefixing your views with "Vw" or "View" is a
helpful reminder that you're dealing with a view, and not
a table. Whatever type of prefix you choose to apply, use
at least 2 letters and not just "V" because a prefix should
use more more than one letter or its meaning can be
ambiguous.
5. VIEWS
Rule #2: View Types
For simple views that just join one or more tables with no
selection criteria, combine the names of the tables joined.
Example: Joining the "Customers" and "StatesAndProvinces"
table to create a view of Customers and their respective
geographical data should be given a name like
"VwCustomersStatesAndProvinces".
6. STORED PROCEDURES
Rule #1: Prefixes or Suffixes
1. Group by the type of CRUD operation
Example: “CreateProductInfo” or “CreateOrder”
2. GRUD operation they perform
Example: “ProductInfoCreate” or “OrdersCreate”
3. Performs an operation like validation , use the verb and noun combination
Example: “ValidateLogin”
6. STORED PROCEDURES
Rule #2: Grouping Prefixes
If you have many stored procedures, you might want
to consider using a grouping prefix that can be used to
identify which parts of an application the stored
procedure is used by.
Example:
"Prl" – Payroll related procedures
"Hr" - Human Resources related procedures
6. STORED PROCEDURES
Rule #3: Bad Prefixes
Do not prefix your stored procedures with something
that will cause the system to think it is a system
procedure
Example:
"sp_", "xp_" or "dt_" – in SQL Server
7. TRIGGERS
Rule #1: Prefixes/Suffixes
To distinguish triggers from other database objects, it
is helpful to add "Trg" as a prefix or suffix.
Example:
“ Trg_ProductsIns “, “ ProductsInsTrg “, “ Products_InsTrg”
or InsProducts_Trg
7. TRIGGERS
Rule #2: Multiple Operations
If a trigger handles more than one operation (both
INSERT and UPDATE for example) then include both
operation abbreviations in your name.
Example: "Products_InsUpdTrg" or "TrgProducts_UpdDel"
7. TRIGGERS
Rule #3: Multiple Triggers
Some systems allow multiple triggers per operation
per table but make sure the names of these triggers are
easy to distinguish.
Example: "Users_ValidateEmailAddress_InsTrg"
"Users_MakeActionEntries_InsTrg".