Database Design – Naming Conventions
What is a name in the database? A lot if you are talking databases (Naming Conventions in Database Modeling). Well, that’s a pretty popular line; however, not all Data Architects would agree with Shakespeare on that!
When talking about “Name,” here is an incident I remember. One day, a new guy in my team created a table and called it “I_WIN_YOU_LOSE”, seriously, he did that! And so were the names of the fields in that table. At first sight, it was funny, as all the queries running against that table were pretty creative. You can imagine some innovative queries on such a table.
Although all of that earned him the reputation of a “Cool Dude”, it was not possible to follow his style in our Data Warehouse. So I had to be the geek & explain to the team how we name database objects here…
An observation I made from this experience was that when we set standards for everyone, progress is rapid. The computer industry has long recognized the value of standardization. When we standardized the formatting of a disk drive, we got drives that both Unix and Windows systems could read, as well as CDs, DVDs, and Flash Drives, all of which have a standard format for information exchange. When we standardized the TCP/IP protocol, we could connect all the computers, and the Internet was born! When we standardized the HTML protocol, the World Wide Web was born. I could give you many more examples, but you get the point. So why can’t we humans follow simple naming conventions? Particularly when it comes to naming tables and columns, we suddenly get creative. It’s almost like we take for granted the authority to be different.
When it comes to data modeling, especially in a multi-tier, team-based, fast-growing environment, the “Name” of an object is crucial, as it defines the object. Naming objects becomes much more than just tagging a word to a face. Naming becomes complex when different people attach different meanings to the same name and also use other names with the same meaning. Everyone has their own style that comes from their personality, personal preferences, and past experiences, like the one in my earlier story.
Let’s understand the principles behind naming conventions in database modeling (database design), with specific industry standards & examples. I am going to make this more generic & not specific to our environment.
3 Principles of Naming Conventions
By combining the words of names in a specific way, standardized data component names are created. The rules will vary by organization, but the basic principles for developing rule sets remain constant. What should I name my database?
Three kinds of rules form a complete naming convention:
- Semantic rules are based on the meaning of the words used to describe
- Syntax rules prescribe the arrangement of words within a name
- Lexical rules concern the language-related aspects of names
I. Semantic Rules:
These rules are based on the meanings of the words used to name data components.
- Subjects: entity or subject terms are based on the names of data objects or subjects that are found in data models (entities) or object models (object classes).
- Modifiers: can be the subject’s properties or qualifiers that are used interchangeably when naming data objects.
- Class Words: describe the type of data that a column or attribute contains. Class words data modeling is words that distinguish an individual category or classification of data or domain. This is a classification of the data type or domain.
II. Syntax Rules:
These rules specify the arrangement of name components. Examples of Syntax Rules are:
- The subject or object term occupies the leftmost position in the name unless it is used as a modifier to another subject.
- Modifier terms follow the subject. The order of the qualifiers in a name is used to make the name complete and transparent to the intended audience. Use subject, property, and/or qualifier terms as needed.
- For columns and attributes, the last term should be the class word at the rightmost position.
III. Lexical Rules
These rules determine the standard look of names.
Examples of Lexical Rules are:
- Nouns are used in the singular form
- Verbs are always in the present tense
- No special characters are allowed
- Underscores separate all words
- All words are in upper case
- Listed/approved abbreviations and acronyms
Industry Standards of Database Naming Conventions
I. Definitions & Common Rules
Entity or Table
An entity is the representation of a distinguishable person, place, thing, concept, event, or state that has characteristics, properties, and relationships. A table is a physical collection of data about a person, place, thing, concept, event, or state. A table may correspond with an entity.
Attribute or Column
A column or attribute contains a specific detail about an entity or table. A column or attribute should not include multiple values, such as arrays or concatenated values.
Common Rules
- The name of an entity/table or attribute/column should enable its audience to identify and locate it within its context. Therefore, each entity/table or attribute/column name must be unique within its context (an entity within its model, a table within a database schema, an attribute within an entity, or a column within a table).
- The name of an entity/table or attribute/column should be a declaration of the classification of the data it contains or will contain. Therefore, it should be a noun or a noun phrase in the singular form and should follow a classification declarative format.
- The name of an entity/table or attribute/column should enable designers, developers, and business personnel to clearly understand what it contains or what to place in it. It should describe its content (what it is), rather than how it is used, processed, populated, or decoded.
II. Formats & Examples of Naming Conventions
The entity or table name should be a noun phrase constructed with the following format:
Subject Modifier
Attribute or column names should be a noun phrase constructed following the format:
Subject Modifier Class
Subject indicates the class of information that the entity or table describes; it provides the proper naming context for the modifier. Subjects are nouns that name things.
Subjects may be composed of several terms or words.
Examples: Employee, Purchase Order, Item, etc.
The modifier is an optional component of the entity or table name that further qualifies the name. The modifier is one or more properties and one or more qualifiers.
Examples: Project Installment, Employee Contact, etc.
A class or class word classifies the type of information being represented by the column or attribute.
Examples: Employee Number, GL Account Number, Purchase Order Status Code
All of this might sound boring to all the Shakespeare types out there. Still, I know for sure that all the Data Architects would agree & appreciate these naming conventions in database modeling (database design)!
After all, this makes our lives easier & organized, and data interchangeable. I will follow this up with a post on data types and data quality rules that change data into information. How to populate missing values? How to check for ranges of values for each field? How to test data quality on the dimensions of completeness, accuracy, consistency, timeliness, etc.
