Is it poor coding practice to have a SQL table that contains a column with the same name?

Question

Example:

CREATE TABLE ErrorNumber
(
     ErrorNumber int,
     ErrorText varchar(255),
)

This can result in queries that look like:

SELECT ErrorNumber FROM ErrorNumber WHERE ErrorNumber=10

Answer 1

I'm assuming that ErrorNumber as a column in the table is a primary key? In this case you could name the table column ErrorNumberID.

I don't know about it being a poor coding practice, but I can't imagine it is doing anything for readability. The example you provided is great at showing how confusing the queries can end up being.

The other thing I'm not sure of is if this (column names being the same as table names) would cause an error? I'm sure it varies from vendor to vendor.

Answer 2

You could stick to default and name your primary keys id.

Answer 3

Is it poor? Maybe slightly but it's nothing major.

Answer 4

I don't think it is a good idea to have a column that share the same name with its table. It's confusing even if it works. Try to rename your table to Error or the column to ErrorNum or even just Num

Answer 5

I agree that is nothing major.

We have skads of tables in an application that have only and id and a "name" field. They are used for drop down lists.

So I set them up like this:

The list name is: State

The table name is : State

The ID is : StateID

The actual state value column is : StateName

It works for us.

In your specific case, I would do as suggested above and just name the table Error.

Answer 6

Yes, it is. The vast majority of simple tables in simple data models should be plural nouns. In your example, the table should be called "errors", with two columns, "id", and "description".

Answer 7

If any database vendor gets confused by that, use another database vendor. Any database should easily parse that without problem.

I agree it's not probably good practice for readability reasons.

Answer 8

CREATE TABLE Errors (
    Number int,
    Description varchar(255)
)

I feel the above is a better naming scheme because:

• The table name now represents what it is.
• The columns do not need the word "Error" in them because of the table they're in.

Answer 9

Its a matter of form. Personally, I make most table names plural, then it would never happen. In this particular case, my table would be ErrorNumbers and the table field would be ErrorNumber ( actually I would use ErrorNumberID )

Again though, its up to you or your companies coding standards. If it is bad form, it is an extremely minor violation. If anything, its the missing ID on the variable name that is more of a violation.

Answer 10

I agree with DWC, and would take it a step further in naming the table something a little more descriptive, as in what type of error table it is. If it is used to store errors that you will use in your code is one thing, and if its a table that logs errors is another. Not sure what you are using it for, so it's really up to you to name it something that makes sense to the context it's used for. When I see a table named "Errors" I am not sure if its a log table, or a lookup table used to find error codes. If it is the latter than perhaps something like ErrorCodes would be a good name.

CREATE TABLE ErrorCodes
(
Id int,
Number int,
Description varchar(255)
)

Answer 11

If a table is expected to comprise more than one row then my preference is to use a collective term as a name. Note this is not the same as takin the singular and appending an 's' e.g. I'd prefer 'Personnel' and 'Payroll' over 'Employees' and 'EmployeesSalaries' respectively. And a name such as 'Entitys' should be taken out and shot :)

In light of this, and considering the columns in your design, I would name the table in question 'Errors'. Now, I know many coders prefer singular terms for table names but that's just a different style rather than 'poor coding practice'.

There are occasional needs for single row tables e.g. a table 'Constants' containing the shared approximation of pi to be used by applications using the DBMS... but then the 'Constants' table I have in mind has multiple columns each for a different constant so it gets a collective term as a name.

Perhaps if your applications only ever used one constant then maybe you could end up with a table called 'Pi'? Well, another style choice I've made is to use UpperCamelCase for table names and lower_case_separated_by_underscores for column names. Therefore, I still be able to distinguish (just about) the table from the column i.e.

SELECT pi FROM Pi;

[And a code parser such as the one here on SO makes the job easier too!]

Actually, I tend to use table correlation names almost exclusively so I'd be more likely to write:

SELECT P1.pi 
  FROM Pi AS P1;

Also consider that some collective terms are spelled the same as the singular term e.g. 'species', 'deer', 'sheep', 'fish' and probably many others not pertaining to the animal kingdom but I'm having a mental block just now :)

So while I can envisage a SQL table that contains a column with the same name even with my preferred data element naming convention, it would be a rare occurrence indeed.

Answer 12

+1 for dwc's succint names.

The problem with "a column name should signify a domain of values" is that it overlooks context. A column name exists only in the context of a table. It's never ambiguous. Its domain is controlled independently of its name.

Does the person exist who cannot distinguish between Errors.Description and Parts.Description and Problems.Description, or who would assume that all columns named Description or Name are drawn from the same holy well?

That said, I don't use generic terms such as Number or ID for primary key columns, for a very different reason: the column needs a new name where it appears as a foreign key.

Suppose error numbers had to be recorded in, say, the Actions table. Actions.Number can't possibly refer to an error number, so we're forced to invent something like Actions.ErrorNumber. If it's going to be ErrorNumber everywhere else, it might as well have the same name where it serves as the key!