Database Naming Covention

General conventions

The following conventions apply to all of the elements of a database:

• All names used throughout the database should be lowercase only. This will eliminate errors related to case-sensitivity.
• Separate name parts by underlines, never by spaces. This way, you improve the readability of each name (e.g. product_name instead of productname). You will not have to use parentheses or quotes to enclose names using spaces as well. The use of spaces in a database name is allowed only on some systems, while the underline is an alphanumeric character, allowed on any platform. Thus, your database will become platform independent.
• Do not use numbers in the names (e.g. product_attribute1). This is proof of poor design, indicating a badly divided table structure. If you need a many-to-many relation, the best way to achieve it is by using a separate linking table. See how here. Moreover, using numbers to differentiate between two columns that store similar information might be an indication that you need an extra table, storing that information. For instance, having a location1 column in a manufacturers table and a location2 column in a distributors table could be solved by creating a separate table that stores all locations, and that is referenced by both the manufacturers and distributors tables via foreign keys.
• Do not use the dot (.) as a separator in names. This way you will avoid problems when trying to perform queries, as the dot is used to identify a field in a specific column. In SQL language, manufacturer_man.address_man means the address column from the table that stores information about manufacturers.
• Do not use any of the reserved words as names of database elements. Each database language uses some words as names for internal functions, or as part of the SQL syntax. For instance, using order as the name of a table that stores product orders from an online shop is bad practice, because order is also used in SQL language to sort records (ascending or descending).
  For a complete list of the reserved words that you should not use when naming the database elements, consult your specific database software manual. See the list of reserved words for MySQL here.
• When naming the elements, do not use long or awkward names. Keep them as simple as you can, while maintaining a clear meaning. It's also a good idea to use names which are close to the natural language: description_prd is certainly a better name for a column that stores product descriptions than dscr_pr or some generic name as field_1.

Database names

Each database must have a name of its own, which should also follow some conventions:

• Use the project name as the name of the database.
• Prefix the database name with the owner name, separated by an underscore. The owner might be a person (e.g. the project manager) or the application for which the database is created. For example, acme_catalog can be a good name for the database storing
  the product catalog of the ACME company.

Table names

Tables are some of the most common elements used in an application, as they store
the columns, and as such are mentioned in each query. Therefore, the following conventions should apply to tables:

• Table names contain the name of the entity that is being defined, followed by a three letter acronym of that name (e.g. category_ctg). Optionally, you can use the same prefix for all tables in the same database. For example, acme_product_prd, acme_manufacturer_prd, acme_category_prd can be tables from the acme_catalog database.
• Prefix tables that define the same larger entity with a 2 or 3-letter acronym that identifies it. For example, e.g. hr_applicant_app, hr_job_job and hr_resume_rsm are
  all tables that belong to the Human Resources Department of a large corporation database.
• Do not use generic prefixes, such as tbl_, or db_, as they are redundant and useless.
• Use short, unambiguous names for each table, restricted to one word, if possible. This way tables can be distinguished easily.
• Use singular for table names. This way, you avoid errors due to the pluralization of English nouns in the process of database development. For instance, activity becomes activities, box becomes boxes, person becomes people or persons, while data remains data.
• Use clear names. Do not overdo it using abbreviations and acronyms. While using a shorter name might help the developers, it makes the meaning less clear to other members of the team. Using clear names makes the design self-explanatory.
• Prefix lookup tables with the name of the table they relate to. This helps group related tables together (e.g. product_type, product_status), and also helps prevent name conflicts between generic lookup tables for different entities. You can have more than one generic lookup table for an existing master table, but which address different properties of the elements in the table.

Column names

Columns are attributes of an entity, describing its properties. Therefore, the name they carry should be natural, and as meaningful as possible. The following conventions are recommended:

• All keys are used for indexing and identifying records. Therefore, it's a good practice to put the id particle in their name. This way, you'll know the field is used as a key.
• The primary key is used to uniquely identify each record. That is why its name should be made up of the id particle, followed by the table name acronym. For instance, for the table product_prd, the primary key is id_prd.
• The foreign key name should be composed by the id particle, followed by the acronym of the referred table, and then by the acronym of the table it belongs to. For example, the idctg_prd foreign key belongs to the products table (product_prd), but it refers to the categories table (category_ctg). This way, the table being referenced is obvious from the key name.
• Each column name should be followed by the 3-letter table acronym. This way, each column has a unique name across the database. Without the table acronym, you would end up having two columns called "name", one storing the product name and the other the manufacturer name. Instead, name_prd and name_man can easily be distinguished.
• Date columns should use the “date_” prefix, and the boolean type columns should use the “is_” prefix. For instance, date_birth stores the birth date of a person, while is_confirmed could indicate the order status for a product in a shop, using true/false values (or 0/1).