Database, Table and Column Naming Conventions

Overview :
Databases are persistent storage mechanisms that are present throughout the lifecycle of any application. Databases are created and maintained by the initial database managers and are continued to be maintained by any new database managers that join the team, thus it is a good habit to keep its characteristics consistent throughout. Naming conventions are a set of guideline that make strong foundations for such a consistent system. These guidelines ensure that the names of database entities are readable, easy to use in queries and do not collide with names of other defined entities or keywords. 

Naming Databases :
When naming databases in a professional environment it mainly depends on what kind of application or website will the database belong to. Suppose you are creating a database for an application XYZ, for a company ABC, then the database would be named ABC_XYZ. Database names are easier to use when short, preferably using abbreviations (Facebook becomes FB), and need not be very descriptive. Building on the same point, IBM states in its documentation that database name or database alias should be a unique character string containing one to eight letters from the set of [a-z, A-Z, 0-9, @, #, $]. You can also add a product’s version to the database name. 

Naming Tables :
Tables represent a group/set of some entity that is usually a real-world object like an employee or consequences of that entity like a project. Tables names are like nouns. Different teams of developers follow different practices while naming their tables but some fundamentals are the key to efficiency. There are following steps for the Naming table are as follows.

Step-1 :
Table names should be descriptive. If you are designing a table to store the data about customers in a grocery shop then the table can be named “Customer” or “Customers” based on your preference. However, you should avoid abbreviations because different people may have different thought process while creating an abbreviation like “Cus” which may not always align with another person’s naming like “Cust”. So to keep things simple use full names and if you plan on using singular or plural names then that should remain consistent throughout the project.

#Creating table Customer
CREATE TABLE Customer (column1 datatype, column2 datatype, column3 datatype, ....);

Step-2 :
You can use underscores to prefix a table name. For example, all vegetarian food tables can be written as “Veg_Food” and non-vegetarian can be written as “NonVeg_Food.”

Step-3 :
Avoid using DBMS-specific keywords as names for your tables like “Order” (ORDER BY). The server or the RDBMS won’t throw any error while naming a table as “Order” but it is a good practice.

Step-4 :
Casing depends all on what the developer prefers and what has been used consistently before. But similar to the singular-plural dilemma, you should stick to one case whichever looks readable and easy on the eyes, either it be PascalCase or camelCase or all lowercase etc.

CREATE TABLE OldMansions (column1 datatype, column2 datatype, column3 datatype, ....);
CREATE TABLE oldMansions (column1 datatype, column2 datatype, column3 datatype, ....);
CREATE TABLE oldmansions (column1 datatype, column2 datatype, column3 datatype, ....);
CREATE TABLE old_mansions (column1 datatype, column2 datatype, column3 datatype, ....);

Naming Columns :    
Columns are different than tables as they can mean more than just a real-world entity. Columns are the attributes that define a real-world entity. Columns may define the departure time of a train or the distance of a planet from the sun, or simply someone’s name. Thus, it is common sense to name a column corresponding to its use. Column names are like adjectives or verbs. There are the following steps for Naming columns are as follows.

Step-1 :
Each column name should be unique. If two columns from different tables serving different purposes are in the same database then use some kind of prefixes that separate the two. 

Owner	Old_Boats	New_Boats
01	BO-25	BO-102
02	BO-26	BO-103
03	BO-27	BO-104
04	BO-25	BO-101

Step-2 :
Column names must not be abstract or cryptic. Use long descriptive names instead of short and unclear abbreviations. 

Step-3 :
The column names must not be very generic. For example, while creating a column that stores the codes of grocery items it would be better to name the column “Grocery_Code” or “ItemCode” instead of just “Code.” You can have a quick look at this column to add suffixes with a specific domain’s prefixes.

Class	Suffix
Code	_CD or _CODE
Description	_DESC
Percentage	_PCT
Balance	_BAL or _BALANCE
Amount	_AMT or _AMOUNT
Frequency	_FREQ
Index	_IDX

Naming Primary Keys :

1. Primary keys serve as the unique identifier for your table, thus it is important to be careful while naming them. If several different tables with different uses have the same name for their respective primary key then it would be very confusing. 
2. Make sure that instead of naming a primary key representing an identity number as ID, use domain-specific names like StudentID or ID_Student. 
3. You can also use suffixes or prefixes with your primary keys to making them stand out from the general column naming. For example, Coordinates_PK or PK_Coordinates.

Naming Foreign Keys :

1. Foreign keys are used as a bridge between two tables, and sometimes among more tables. Thus, it is good to name your foreign key with the same name consistently throughout the database to avoid any confusion. 
2. Similar to primary keys, you can add prefix or suffix to a foreign key name, like RoomNo_fk or FK_RoomNo.
3. At the end of the day, no one is going to force you to follow these conventions while creating a database. But following even some of these will make your database better for you as well as anyone else who might work on it in the future.