Data Wise Business Solutions

Naming can be very hard to standardize on as everyone has their own way of naming variables, columns, tables, and etc. Many times, there are way too many rules to remember. I am going to propose what I think is a consistent way of naming.

History: If you look at history of naming objects back to the mainframe days. There were physical constraints that designers had to live with. Today, you would never name a column FN (first name) as it is too cryptic. But, when storage was very expensive, it was done. Many systems had restrictions on number of characters. In the early days, the limit was up to 6. Even the first version of dBase had a limit of 8 characters. I remember one system that didn't use ASCII and they could save a bit or two by only having upper case letters.

Today, we really don't have physical constraints. Most systems allow 256 character names. Some even allow Unicode characters. Oracle is the only exception to the rule.

What do you think of these examples: FIRSTNAME, FIRST_NAME, firstname, first_name, First_Name, FirstName, firstName. How about these: FrstNm, FstNme, fn, FN, Fn. I am sure you have picked out your favorite.

Before we have a standard, we have to look at the development language. Is the language case sensitive? Java and c++ are. PHP and ASP are not. If the language is case sensitive, many developers like to have all of the names in lower case (firstname) or proper case (FirstName) or the modified proper case (firstName).

Should the development language drive the naming convention for databases? The answer is maybe. It might be easier to have a standard that will work across all development efforts.

What ever you decide to be your standard, just be consistent.

Primary Key

Simple Name:   <Table Name> + PrimaryKey
Example:  ClientPrimaryKey

Alternate Name:  <Table Name> + pk 
Example:  ClientPk  or Client_pk

Primary keys in most database systems have two functions:  1) Insures the data must is unique.  and 2) an index is also created.   

Foreign Keys

A foreign Key is the database engine’s way of maintaining proper data relationship between the parent and child table.    For example, lets say we have two tables.  Order and OrderDetail.   The relationship is that there is 1 record in the parent (Order) and many records in the child (OrderDetail).  

Simple Name:  <Table Name> + ForeignKey + sequence
Example:  OrderDetailForeignKey1,  OrderDetailForeignKey2

Alternate Name: <Table Name> + fk + sequence
Example:  OrderDetailFk1,  OrderDetailFk2 or OrderDetail_fk3

NOTE:  with the creation of a Foreign Key, the underlying index is not created.   You must also create a index.

Syntax (will work with all database engines):
ALTER TABLE OrderDetail

ADD OrderDetailForeignKey1

FOREIGN KEY (OrderID)

REFERENCES Order (OrderID);

CREATE INDEX OrderDetaiIindex1 on Order (OrderID)

Defaults

A default value is used by the database engine when a value of a column is NULL.    Please see another article on the subject of not using NULLS:   Real World Use of NULLs

For number data type,  It is a must to define an unknown value as zero.  

Simple Name:  <Table Name> + Default + sequence
Example:  OrderDetailDefault1,  OrderDetailDefault2

Alternate Name: <Table Name> + df + sequence
Example:  OrderDetailDf1,  OrderDetailDf2 or OrderDetail_df3

Defaults are typically defined while the table/column is being created.

CREATE TABLE OrderDetail (
   OrderDetailID int not null,
   …
   Amount real default 0
   …
);

The Standard to Think About:
There are many articles on the web talk naming conventions and none will be the same.   However there is a few common attributes that are the same.   They are:  1) don't use underscore (_) to separate names and 2) don't use all upper case characters.   Everything else is open to personal preference.     

A Standard: In the following, we will have a logical and consistent discussion of standard naming convention.  

A few consistent rules:

• No underscores (_)
• No s at the end (i.e. Names).  Every database or table will hold more than one item, plural is assumed.  Name should be singular.  I.e.  Name table will hold names.
• No Abbreviations.   All modern database systems allow more characters for a name they you will ever use.   For example:  "nbr" and "no"  spell out number.   "Amt"  spell out amount.   
• Be consistent.  

Databases
What data does it hold?   What is the system name?   Name the database.  Remember the rules above.  

Tables

• Simple Name:   Use Proper case. (i.e.  Name,  Address,  ClientName, and etc)
• Singular naming -- No s at the end of the name.
• Avoid prefix or suffix of List, Data,  Info.   (i.e don't use NameList).  All tables are a list or data or some sort as their nature.
• For Junction tables (many to many) or Parent/Child tables (1 to many) use all names that define the relationship.   (i.e.  Order (parent), OrderDetail (child))

Columns

• Simple Name:   Use Proper Case (i.e. FirstName, LastName, and etc)
• DO NOT: have the table name part of the column name.   If the table is named Client, do not have a column named ClientCode.  It should just be Code.
• Foreign key Column names should identify the parent table name.  Using the Client example above.  It is ok to have ClientCode in an Invoice table.  
• For Primary Keys, use ID suffix.  The only inconsistency in naming will be this:   Don't just use ID as the column name.  Use the table name too. Example: OrderID.    The reason is simple and logical.  When writing sql code,  PK and FK (which are used all of the time) are is easier to read when you can type OrderDetail.OrderID = Order.OrderID compared OrderDetail.OrderID = Order.ID
• Don't use abbreviations.  
• For consistent data typing, here are some suggestions. 

* this data type you will see many different opinions. Some standards suggest prefixing the column name with Is (i.e. IsActive). Some use the word flag or bit. Recommend using a suffix.

I have been getting into biking more and more.   Biking is a good exercise.   I have read health articles that just 3 hours a week can reduce hart-attack and stokes by 50%.

I purchased a Word Combination Lock for the bike.    It has four dials with ten letters each.   I couldn’t find a word I liked so I searching the web for samples.  But I didn’t find any lists.  

Now for the real purpose of this blog.   Using Regular Expressions.    I used regular expressions to produce a list of possible combinations.     This is how I did it:

1) first off, you need a list of English words.    I did a search and found a huge list of words (WordList.txt).  There was a lot of garbage in the file I found, so I modified it a bit. 

2) Create a table to hold the word list:
    Create table wordlist (word varchar(255));

3) Import the list into your database.    There is many ways to get data into your database.    I used toad's import tool.

There are duplicates in the list.   That is why there isn’t a primary key defined.

4) Time for the query:

Letter options for each dial.   The default combination is SHED.    As you can see from the list, there is runs, fast, bike, loop, play and well.     

Using the letter combinations for each dial, we can use simple regular expression to produce a list of words that can be formed.  

See http://en.wikipedia.org/wiki/Regular_expression for definition and history of regular expression.

The Query:

select distinct word
from wordlist
where ucase (word) REGEXP '[STDMRFBLPW][HWRYUAIOLE][ERMTNSKOAL][DGKMSTEPYL]'
and length(word)=4;

Looking at the query.   

1. select distinct word
Remember there are duplicates in the word list.  Distinct will eliminate duplicates the list.   Note, distinct shouldn’t be used in any production environment

2.  where ucase (word)...
It is good practice to match the case that you are expressing. Ucase will convert all words in the table to upper case.    

Do note, that some database engines will not use an index when changing case.   Thus producing a slower query.

3.  Looking at the simple version ...word regexp...  is similar to ...word = 'SHED' or word='SHEG' and etc.   looking at each character that matches each dial letter.

For the Microsoft SQL Server users:   there really isn’t a good way to do regular expression.    Click here for a goole search that might help.

The Result.  The query produced a list of 5026 possible words.    See Word_Lock_Sample_List.txt for the list.   The list isn't the best. The source file did have non-existent words. 

Data Wise Business Solutions, provides companies of any size Developers and Database professionals.

We specialize in Developers that program in PHP and Java.   For the data side, developers and Database Administrators for MySQL and Microsoft SQL Server.

• For hard to find jobs, we work with a retainer
• For most positions we charge a per hour amount

I  have discussed with other articles that a good database design shouldn’t use nulls.   See Real World Use of nulls.  Nulls can cause issues in application development.

I was helping out a friend with a query and noticed that he had written a check (in WHERE) for a column with two conditions.   One of them being null.   The column was a status column (I.e true/false).    First off, All status columns should have a default value of zero (false).   

To help him with his query and worry about fixing the database later I helped him to write his query better.

Before:
    Select...
    From...
    Where status <> 1 OR status is null. 

On the top of my list of best practices is never to use OR.   He didn’t think he had another option until I showed him this version:

After:
    Select...
    From...
    Where coalesce(status,0) <> 1

Notice NO OR.   Coalesce will return the first non null.   In above example, if status is null, it will then return 0.  If status was 0 or 1, it would return that value.

Should also note that coalesce will work on all database engines. 

Now lets fix the database issue.   How to add a default to an existing column?  
First lets look at a sample table.  Col1 and Col2 allow nulls:

CREATE TABLE Table1 (
   Table1ID INT AUTO_INCREMENT NOT NULL,
   Col1 INT,
   Col2 VARCHAR(50),
  PRIMARY KEY (Table1ID)
);

Put some data into that table (mysql code):
insert into Table1 (Col1, Col2)
VALUES (1,NULL), (NULL,'A'), (null,null);

we get:

select * from Table1;

Table1ID    Col1    Col2
1            1      NULL
2            NULL   A
3            NULL   NULL

Now lets update col1.  Set a default and make it not nullable.

-mysql
ALTER TABLE Table1
 CHANGE Col1 Col1 INT(11) NOT NULL DEFAULT '0';

--mssql  v2000 and 2005
ALTER TABLE Table1
 alter column Col1 INT NOT NULL DEFAULT '0';
 
--mssql  v2000 and 2005
ALTER TABLE Table1
 alter column Col1 INT NOT NULL DEFAULT '0';

--mssql 2008
ALTER TABLE Table1
add constraint dfTable1 default '0' for Col1

Looking at the existing data you will see that Col1 values that were once null are now 0
select * from Table1;
Table1ID  Col1  Col2
1         1     NULL
2         0     A
3         0     NULL

Triggers

Triggers exist in all common database engines.   There are some good reasons to use them.  But in most cases, they are used to fix bad application code.   So a good rule is not to use them.

Simple Name:  <verb> + <Table Name> + trigger + sequence
Example:  InsertUpdateOrderDetailTrigger1

Alternate Name: <verb> + <Table Name> +tr + sequence
Example:  InsertUpdateOrderDetailTr1 Or InsertUpdateOrderDetail_tr2

Views

A view is a pseudo table.  A table that is made of a select statement.  Read Only.   While some database engines allow for update, it is more common to select from it.

There are arguments on how to properly name a view.  Some people say that a view should be prefixed with a “v” or “view”.    Some have a suffix of “S”.  S for plural (i.e. Clients)

For this document we will not have a prefix.  We will have a suffix

For example, we will have a view that will return Orders and the Order Details.    The view name would be called OrdersDetails

Stored Procedures

There have been a lot of discussing on using or not using Stored Procedures.     All common database engines support stored procedures.     We will not discuss the pros/cons of using them.   

Unlike other database objects, Stored Procedures do not have to be tied to a table.   The Name of the stored procedure should represent what it does.     

Basic naming:   ...<Verb><Noun>.   Where <Verb> is action:  insert, update, delete, Select and <noun> is the item the action is to be performed on.    

For example:   Inserting records into the client table.  The <verb> is Insert, and the <noun> is Client.  

Simple Name:  procedure + <verb> + <Noun>
<verb>

insert or ins
update or upd
delete or del
select or sel
get (same as select)
put (combined insert/update)

Example:  procedureInsertClient,  procedureDeleteOrderDetail

Alternate Name: sp + <verb> + <Noun>
Example:  spInsertClient,  spDeleteOrderDetail

Note, when naming stored procedures on MS SQL, do not use sp_.  That is used for the system database and the database engine will look there (and not find) before looking at your database.

Some times, there is a need to insert records into a table that has a auto number increment column. If you are needing to load one or many records and have to preserve an ID you need to turn off the auto numbering. Another reason might be that you need a zero record for a data warehouse. Below are two examples. One My Sql -- which has a few more lines of code. The other is Microsoft SQL Server.