0

In a question I asked yesterday:-

SQL SELECT DISTINCT Puzzle

The user spectralghost mentioned that my column name (e.g.TypeID) was a terrible naming convention. Can you guys elaborate for me why this is and let me know what kind of naming conventions 'should' be used for tables and columns?

Community
  • 1
  • 1
Mat Richardson
  • 3,576
  • 4
  • 31
  • 56
  • possible duplicate of [Database, Table and Column Naming Conventions?](http://stackoverflow.com/questions/7662/database-table-and-column-naming-conventions) – Tony Oct 17 '12 at 13:04

3 Answers3

1

You refer to this:

LEFT JOIN Document d on dt.DocumentType=dt.TypeID --terrible naming convention....

The problem with this is that the column that is semantically the same, has different names in two tables. Whatever it is, is advised to be the same, to be able to read your scripts better. You have to do a lot of extra thinking when trying to keep in mind all of these, if you don't follow this rule, as the number of the tables start to grow...

ppeterka
  • 20,583
  • 6
  • 63
  • 78
  • 2
    +1 also I prefer to not repeat the name of the table in the fields name. (Apart from the PrimaryKey and ForeingKeys) – Steve Oct 17 '12 at 13:01
  • I think that `LEFT JOIN Document d on d.DocumentTypeID=dt.ID` is more readable (Primary key in my tables are always called ID), but it a humble opinion – Roman Pekar Oct 17 '12 at 13:03
  • 1
    @RomanPekar I worked with both approaches, and I found your suggestion a bit harder to follow when very large queries were created. It is much much better however than having no system. It is true, that your suggestion means quite less typing, when names are starting to get long... – ppeterka Oct 17 '12 at 13:06
  • In my case it's also means I can write dynamic queries and always refer to PK of any table. I have massive database-level audit system based on triggers and special tables, now it's only about a two pages of code in it (triggers and sp). If I didn't have a conventional ID in tables it could be more complex. It's just one example. – Roman Pekar Oct 17 '12 at 13:09
  • @RomanPekar in this case I understand your point. – ppeterka Oct 17 '12 at 13:11
0

Looking at that question, I would guess that the comment was not about the name of the column TypeID but rather that the relation was between a column called Type and one called TypeID. Without wanting to put words into the mouth of the person who made the comment, I see these problems:

  1. Type is likely to be reserved word, now or in the future. In general, words with common meanings in programming languages or database models should be avoided.
  2. When you have one column that is an ID of something (in this case of a Type) and another column that is the thing itself, it's hard to understand whether the relation is happening correctly or not. In this case, it's not clear if Type holds the ID or the name of the type. If the column on the FK side refers to the TypeID column it should at least contain the name TypeID.

Other issues not directly related to the naming convention:

  1. If your system includes things other than documents, TypeID is not going to wind up being a very descriptive name. Perhaps DocTypeID would be clearer, especially in the foreign key side of the relation.
  2. You could give serious consideration to eliminating the TypeID column from the document type list entirely, and simply using the type name as the primary key. As long as these are not mutable it will save you a JOIN whenever you need the type name but no other information from the type table.
Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
0

Really only that user can answer, as 'terrible naming convention' is his personal opinion. The line in question was this

LEFT JOIN Document d on dt.DocumentType=dt.TypeID --terrible naming convention....

I think he means that the foreign key should have the same name in both tables - instead of DocumentType, the field should have been named TypeID, so the line would have read

LEFT JOIN Document d on dt.TypeID=dt.TypeID
No'am Newman
  • 6,395
  • 5
  • 38
  • 50