Here’s another of Phil Factor’s SQL Smells. “Using the same column name in different tables but with different data-types”.
At first glance this seems harmless enough, but it can cause all sorts of problems. All of them are avoidable. If you are an analyst, make sure you are not encouraging this to happen. If you creating the Physical Model or DDL for a database, “Just don’t do it!”
As Phil Factor says, this is: “an accident waiting to happen.”
Two “rights” can make a “wrong”!
The problem here is not “using the same column name in different tables”. That is perfectly ok. Similarly, using “different data-types for different columns” cannot be wrong. That’s exactly what you should expect to do.
The problem is doing both at the same time. The issues are: practical and technical.
The Practical Problem of the same column name with different data-types
Any human user is likely to think that the same name refers to the same type of thing. They won’t check that the definitions of both “names”. No amount of “procedures” or “standards” will make them do anything different.
Sooner or later this will cause one of the technical problems.
The Technical Problems from the same column name with different data-types
Technical problems will occur when a value moves from one column to the other, or when comparing the two columns. Data may be truncated and those data transformations cost effort.
These problems may not manifest themselves immediately. The consequences will be data-dependent bugs and poor performance.
The Solution to this Issue
This smell and the associated problems can be avoided by following some simple rules:
- If two columns refer to the same thing (like a foreign key and a primary key), make sure they are the same data type.
- If two columns refer to different things, then give them distinct names. (Do not resort to prefixing every column name with the table name. That’s horrible)
- Having columns with have different names and the same data-type is perfectly OK.
“Using the same column name in different tables with different data-types” in an SQL database is simply “an accident waiting to happen.” It is easily avoided. Don’t do it and don’t do anything to encourage it.
The next article is about the smells which come from dates and times.