how to name fields(columns) for SQL Table

Hi Bembi,
   Thank you for your prompt reply. It's very helpful for me. Let me think about it.

  Thanks.

  jeff

Hi slightwv,
>>>>On large design efforts with a few hundred tables and a few thousand columns, you might forget the acronym for 'General accident insurance' a week later when you need another column in some other table.
  It really could happen to me. I must establish the same naming rules for every column or table, even the database is so complicated. It's very important for keeping database structure well and easily readable.
>>>>In other words:  Be consistent!!!
You are right. I don't want to have a complete mess about the database structure.

Thanks.

jeff

This is where the table and column comments I mentioned and Tomas later mentioned helps.

If you want to know if you have previously come across a table or column referencing  'General accident insurance', just search the descriptions/comments...

Then it is a reminder that you used an acronym like GAI or whatever you decided on.

They help create a self-documenting database.

I will also warn you to not get too fancy in your naming conventions!!!

You won't want to end up with a bunch of columns like:  GAI_ASD_B_TRE_PLM_Q

and just an FYI in case you didn't already know.


Using underscores is called Snake Case:  https://en.wikipedia.org/wiki/Snake_case

Hi Tomas Helgi Johannsson,
  Thank you for all of the help. I must spend more time on research about your suggestion. 

 best regards,

jeff
 

Hello,
according acronyms, I means well known ones. Each business usually have a complete book of them, because people are too lazy always to write half sentences.

Just catched an article called "Bad names make you open the box". This article is more related to names for programming, so procedure names etc. The autor just describes, that if you can not read out of the name, for what it is used for, then you have to look into the code to be aware what is inside.

But it is the same with filednames. A name should be self explaning, but short. So, if the name doesn't do it, you have to look into documentation or whatever. Its a compromise anyway. But if you have acronyms, it is an option to use them.
Creating new ones can really have the effect, that you forget them after a week.

If you are not able to remember columns reason/meaning from their shortened names then you may add metadata describing the column purpose and possible values etc.

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='dictationHeader' AND COLUMN_NAME='Active')
 BEGIN
  ALTER TABLE dbo.dictationHeader ADD Active BIT DEFAULT 1  NOT NULL
  exec _CreateDescription 'dictationHeader', 'Active' , 'Active = false for historical data'
 END
GO

Select allOpen in new window

We are generating HTML help file from these metadata so they are easily accessible to everybody interested. Column names are not so important then BUT to follow given rules and naming consistency is very important.

The SP code:

ALTER PROCEDURE [dbo].[_CreateDescription] (@TableName SYSNAME, @ColumnName SYSNAME, @Description VARCHAR(7500))
AS
SET NOCOUNT ON
IF EXISTS (SELECT * FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TableName, 'column', @ColumnName))
  EXEC sp_dropextendedproperty 'MS_Description', 'user', 'dbo', 'table', @TableName, 'column', @ColumnName
EXEC sp_addextendedproperty  'MS_Description', @Description, 'user', 'dbo', 'table', @TableName, 'column', @ColumnName

Select allOpen in new window

Hi pcelba,
>>>If you are not able to remember columns reason/meaning from their shortened names then you may add metadata describing the column purpose and possible values etc.
   Another topic of concern to me is that I might forget the acronym for 'General accident insurance' a week later when I need another column in some other table. Just slightwv said. After all of the programmers create a few hundred tables and a few thousand columns, i.e Some one name  'General accident insurance rate of labor insurance' to GAI_rate_LI ,and some one name it as GAIRateLI in different systems. It will bother all of us.
   Are there management system to manage the naming convention? For example, After we define the column name of 'General accident insurance rate of labor insurance' as GAI_rate_LI to the management system. All of the programmers must follow the column name which established in the management system, except the programmers can't find any column name which they want to establish in the management system.
   The SP code  can work. But if we use management system to manage the name rules, can it work better?

   Thanks.

jeff

>>Are there management system to manage the naming convention?

I'm not aware of any but that doesn't mean they don't exist.

I just wanted to mention this is where column descriptions will help.  People can look through the exiting descriptions.

Hopefully you are using some modeling tool.  This can help as well.  Especially when you create foreign key relationships.  If someone creates a FK between GAI_rate_LI  and GAIRateLI, it should make them go "hmmmmmmmm........this doesn't look right".

Hi slightwv,
>>>where column descriptions will help .
Ya, but if I have thousands of columns in SQL database. It will be another problem.

  Thanks.

jeff

If it is that big of a concern for you, the solution is simple: Developers cannot make up their own names/tables/columns.

I've worked on larger products that had a Data Modeling Team.  They were responsible for maintaining the model and conventions.  When something "new" was needed, you talked with them to negotiate how it gets implemented.

Left to their own, many times, developers can stray........  I've seen quote a few examples where the "easy path" to solve their problem is:  I need a new table or I need a new index.  They don't know that another table might already exist that would just need a new column or three to do the same thing or another index already exists that just needs a new column.

Hi,
  I decide to develop management system to manage the naming convention. Can I transfer the property of every field for every table in SQL schema, including Key, Name, Data, type, Null, Attributes, References, and Description into Excel.

  Thanks.

  jeff