0

This debate has been done to death here, and I particularly enjoyed reading this thread. However, I just wanted to ask one particular question and that thread is now closed, so here it is.

I'm wondering what people's opinions are on column names. The broad consensus seems to be to name the table's primary key in the format ID, e.g. CustomerID. That's what I do.

But what about the other columns? I tend to prefix every column name with the table name, e.g. CustomerFirstName, CustomerDOB, etc. That feels comfortable to me, but seems uncommon. Leaving off the table name bothers me for two reasons. Firstly, I worry that, as the database gets very big, there will likely be columns in different tables with the same name and it might get difficult to differentiate them, especially when debugging, and secondly it bothers my OCD to have the first column (the primary key, usually) begin with the table name but the others not.

Are there any norms or principles that can help in choosing best naming rules? One other thing, though. In my convention of prefixing every column name with the table name there arises a problem - foreign key columns. It makes no sense to have a column called CustomerStoreID to hold the StoreID foreign key, so I just call it StoreID. But then that's breaking the convention, so is that proof that the convention was poor in the first place? How do you name your foreign key columns?

Community
  • 1
  • 1
Philip Stratford
  • 4,513
  • 4
  • 45
  • 71
  • I use the table name / column name concatenation, and the foreign keys named for their table name / column name. It's not breaking the convention. – Gilbert Le Blanc Aug 29 '13 at 16:58
  • 1
    This is a Question and Answer type of forum, and not a Discussion forum. Soliciting opinions and discussion is generally not viewed with favor... – RBarryYoung Aug 29 '13 at 17:06
  • Database naming convention is supported by ISO norms, so I do not consider this is a 'primarily opinion-based' question! – Philippe Grondier Aug 29 '13 at 17:25
  • @PhilippeGrondier The words that you edited out, "*So, opinions on that would be welcome.*" were a direct solicitation of opinion. With the changes that you made, it seems potentially OK (though still marginal), so I will vote to reopen. – RBarryYoung Aug 29 '13 at 23:22
  • 1
    Thanks for all the comments. Given that there seems to be a definitive answer (ISO 11179) I guess this is a question that can be answered factually rather than just with opinion, even if the way I worded it initially did solicit debate. I don't mind if it's closed, it seems like the answer has already been given anyway. Now I'm off to try and translate ISO 11179 into English... – Philip Stratford Aug 30 '13 at 08:59

1 Answers1

3

This is entirely the decision of the individual. Everyone tries to keep the name of the column which is easy to identify and which can easily be inferred.

As such there is no standard for the naming convention of the columns in database. Every individual keeps the name of the column which can be easily tracked and understood.

I am just taking an example:-

Lets say you have a Table named as Employee.

And in this table you have to store the information of the employees including there name and contact details.

So if you name the columns as Emp_First_name, Emp_Last_Name, Emp_PhoneNo, Emp_EmailAdreess. Then this is more readable and any person who will use your table in future will understand the columns without any explanantion.

prefixing every column name with the table name there arises a problem - foreign key columns.

I dont think so. As it is a common practice that if there is a foreign key on any column then you prefix that column with FK_XXXXX. So anyone can track that it is a foreign key

Similarly if you create a INDEX then people generally add a prefix IDX_XXXX to mark that this is the name of the index.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • you are done... enjoy... – Fahim Parkar Aug 29 '13 at 17:21
  • I know where you want to point. I have read this:- http://sqlserversamples.codeplex.com/ But what I have seen is that generally people follow column names as per there convinience. Correct me if I am entirely wrong?? – Rahul Tripathi Aug 29 '13 at 19:39
  • @PhilippeGrondier:- And certainly this is the reason why this question is closed as opinion based. I know many will not agree with my answer as it is really a **OPNION** based. ;) – Rahul Tripathi Aug 29 '13 at 19:42
  • check ISO Norm 11179. It should give you some directions about data representation, and specifically naming rules. Do not rely only on the Wikipedia page .... – Philippe Grondier Aug 29 '13 at 20:05
  • @PhilippeGrondier:- As I already said Sir that I am not denying that what you are saying is not correct. I am just trying to say that people generally take the name of the column as per there convenience. Although I am again saying you are right. :) This discussion has already been in SO before. Check this Thread:- http://stackoverflow.com/questions/7662/database-table-and-column-naming-conventions – Rahul Tripathi Aug 29 '13 at 20:09