Database naming structure
A Naming Scheme for Database Tables and Fields
Why is There a Problem?
Perhaps the most common practice in database naming is to use all one case (upper or lower) with words separated by underscores, and heavily abbreviated. Fixed case names have become the standard because database systems are traditional case-insensitive in table and field names (or required fixed case). It is also a holdover from academia where use of a single case is common not just in database design, but in programming languages (for example, C language is traditionally taught in lower case and FORTRAN in upper case). Separating words with underscores is a natural result of fixed case names and also is commonplace in academia. The heavy use of abbreviations is a holdover from the days of dBase and other systems where there was a stringent limit on table and field name length. Many database designers schooled in the older systems still retain this use of abbreviated names in a severe case of the psychological principle of learned helplessness.
Most database books still follow these principles. Consequently, even a recent book on SQL Server 2005 uses examples of tables and fields such as:
Table: customers
With fields:
1. cust_id
2. first_name
3. last_name
4. addr1
The SQL for a join on a similarly named orders table would look something like this:
SELECT customers.*, orders.*
FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id
There are several problems with such a naming scheme:
1. It is unnecessarily unreadable by humans and results in messy SQL statements
2.
The join results have two columns named cust_id - one from customers and one from orders. While this is not an insurmountable problem, it is best avoided with a proper naming scheme.
3. It does not lend itself well to automatic code generation programs or Object Relational Mapping (ORM) technologies such as Hibernate.
I have been developing database systems for over twelve years. My work includes custom applications, web-based database, and "shrink-wrapped" applications. I have been the lead designer for the database and had to suffer my own early mistakes. I have been forced to work with database designs developed by others - some good and some bad. And I have dug into the details of database designs for competing products in order to import their data into my company's products. Finally, I have developed an extensive set of automatic code generation tools that allows me to develop and modify robust database applications quickly.
From this experience, I have come up with a database naming system that I have used for at least five years on multiple projects and found it to be very satisfactory. Even if you don't want to adopt every feature of this naming scheme, I am confident that you will find some useful naming tips in this article.
My database naming scheme has some basic properties:
* I use mixed case names.
* I do not use underscores. Mixed-case makes underscores unnecessary and once you are used to omitting them, they look ugly and it is easier not to type them.
* I prefix table names to indicate one of three different types of tables.
* I prefix field names with a three letter code to indicate which table owns the field.
* I use GUID strings as primary keys.
I will explain my reasons for each below.
Naming Tables
In most databases, there are three types of tables:
1. Data Tables - Certainly all tables in a database contain data, but I am using this term to refer to the tables that actually store the data for which we were motivated to build the database to begin with, such as customers, orders, or products. For example, a customers table would contain information about customers with fields such as name, address, and phone. Customers is a data table - unlike these other table types.
2. Link Tables - Link tables do nothing more than connect two key fields from two different data tables to form a many-to-many relationship. For example, you could have a many-to-many relationship between a vendors table and a products table because each vendor could support multiple products and each product could be sold though multiple vendors. This would require a third table to link the vendors to the products.
3. Picklist Tables - It is common to have tables that contain a list of choices for fields in a data table. For example, you may have a status field in you vendors table. Values for vendor status could be selected from yet another table. I refer to these types of tables as "picklist" tables because they allow users to pick from a list.
In my naming scheme, I like to prefix each table name with one of three prefixes to indicate the type of table. I use the following prefixes:
Data Tables - I use the prefix tbl. So remembering my rules for mixed case and no underscores, you could have the following data tables:
* tblCustomer
* tblOrder
* tblOrderEntry
* tblVendor
* tblProduct
Link Tables - I use the prefix link. So to link vendors with products, you would have a table linkVendorProduct.
Picklist Tables - I use the prefix pltbl. So for vendor status, you would have a table called pltblVendorStatus. If you also have a table for customer status, you could have pltblCustomerStatus.
Advantages
I find that this table naming system has several advantages.
1. Obviously, it is very easy to tell from the table name what kind of data the table contains.
2. Every database application I have ever seen (such as Microsoft Access or SQL Server), lists your tables in alphabetical order. Using this prefix scheme causes your tables to be grouped by type when displayed alphabetically.
3. If you develop any kind of automatic code generation tools, it is easy to programmatically determine from the table name what kind of data the table contains. You simply check the prefix.
Singular/Plural Names
Note that in my data tables above, all table names are singular, i.e. tblCustomer rather than tblCustomers. Whether you prefer singular or plural names, you should always use one or the other consistently. I prefer singular because it seems cleaner to me.
Why is There a Problem?
Perhaps the most common practice in database naming is to use all one case (upper or lower) with words separated by underscores, and heavily abbreviated. Fixed case names have become the standard because database systems are traditional case-insensitive in table and field names (or required fixed case). It is also a holdover from academia where use of a single case is common not just in database design, but in programming languages (for example, C language is traditionally taught in lower case and FORTRAN in upper case). Separating words with underscores is a natural result of fixed case names and also is commonplace in academia. The heavy use of abbreviations is a holdover from the days of dBase and other systems where there was a stringent limit on table and field name length. Many database designers schooled in the older systems still retain this use of abbreviated names in a severe case of the psychological principle of learned helplessness.
Most database books still follow these principles. Consequently, even a recent book on SQL Server 2005 uses examples of tables and fields such as:
Table: customers
With fields:
1. cust_id
2. first_name
3. last_name
4. addr1
The SQL for a join on a similarly named orders table would look something like this:
SELECT customers.*, orders.*
FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id
There are several problems with such a naming scheme:
1. It is unnecessarily unreadable by humans and results in messy SQL statements
2.
The join results have two columns named cust_id - one from customers and one from orders. While this is not an insurmountable problem, it is best avoided with a proper naming scheme.
3. It does not lend itself well to automatic code generation programs or Object Relational Mapping (ORM) technologies such as Hibernate.
I have been developing database systems for over twelve years. My work includes custom applications, web-based database, and "shrink-wrapped" applications. I have been the lead designer for the database and had to suffer my own early mistakes. I have been forced to work with database designs developed by others - some good and some bad. And I have dug into the details of database designs for competing products in order to import their data into my company's products. Finally, I have developed an extensive set of automatic code generation tools that allows me to develop and modify robust database applications quickly.
From this experience, I have come up with a database naming system that I have used for at least five years on multiple projects and found it to be very satisfactory. Even if you don't want to adopt every feature of this naming scheme, I am confident that you will find some useful naming tips in this article.
My database naming scheme has some basic properties:
* I use mixed case names.
* I do not use underscores. Mixed-case makes underscores unnecessary and once you are used to omitting them, they look ugly and it is easier not to type them.
* I prefix table names to indicate one of three different types of tables.
* I prefix field names with a three letter code to indicate which table owns the field.
* I use GUID strings as primary keys.
I will explain my reasons for each below.
Naming Tables
In most databases, there are three types of tables:
1. Data Tables - Certainly all tables in a database contain data, but I am using this term to refer to the tables that actually store the data for which we were motivated to build the database to begin with, such as customers, orders, or products. For example, a customers table would contain information about customers with fields such as name, address, and phone. Customers is a data table - unlike these other table types.
2. Link Tables - Link tables do nothing more than connect two key fields from two different data tables to form a many-to-many relationship. For example, you could have a many-to-many relationship between a vendors table and a products table because each vendor could support multiple products and each product could be sold though multiple vendors. This would require a third table to link the vendors to the products.
3. Picklist Tables - It is common to have tables that contain a list of choices for fields in a data table. For example, you may have a status field in you vendors table. Values for vendor status could be selected from yet another table. I refer to these types of tables as "picklist" tables because they allow users to pick from a list.
In my naming scheme, I like to prefix each table name with one of three prefixes to indicate the type of table. I use the following prefixes:
Data Tables - I use the prefix tbl. So remembering my rules for mixed case and no underscores, you could have the following data tables:
* tblCustomer
* tblOrder
* tblOrderEntry
* tblVendor
* tblProduct
Link Tables - I use the prefix link. So to link vendors with products, you would have a table linkVendorProduct.
Picklist Tables - I use the prefix pltbl. So for vendor status, you would have a table called pltblVendorStatus. If you also have a table for customer status, you could have pltblCustomerStatus.
Advantages
I find that this table naming system has several advantages.
1. Obviously, it is very easy to tell from the table name what kind of data the table contains.
2. Every database application I have ever seen (such as Microsoft Access or SQL Server), lists your tables in alphabetical order. Using this prefix scheme causes your tables to be grouped by type when displayed alphabetically.
3. If you develop any kind of automatic code generation tools, it is easy to programmatically determine from the table name what kind of data the table contains. You simply check the prefix.
Singular/Plural Names
Note that in my data tables above, all table names are singular, i.e. tblCustomer rather than tblCustomers. Whether you prefer singular or plural names, you should always use one or the other consistently. I prefer singular because it seems cleaner to me.
Replies to This Discussion
-
Permalink Reply by Shakeel Shrestha on -
Good article to read. I look more such article and tips from u.
About
Translate This Network
CNN Tech
Smashing Magazine
Events
Videos
Music
© 2010 Created by Shakeel Shrestha on Ning. Create a Ning Network!
