CODEWITHSUNDEEP
c#sql-serverwinforms
MadBoy
MadBoy

Reputation: 11104

Should column names be unique across all tables?

In most of my databases that I have created I've always named my column names by pre-appending the table name. For example:

Person Table
 - PersonID
 - PersonName
 - PersonSurName
 - PersonTimestamp

Customer Table
 - CustomerID
 - CustomerName
 - CustomerSurName
 - CustomerTimestamp

As opposed to having 

Person Table
 - ID
 - Name
 - SurName
 - Timestamp

Customer Table
 - ID
 - Name
 - SurName
 - Timestamp

But I was wondering if it's really the best, most convenient and self explaining later down the road. Maybe some columns like timestamp should be better left as Timestamp in all tables? Are there any general good-practice about it? I'm using this databases in C# / WinForms.

Upvotes: 5

Views: 3687

Answers (7)

Aaron J Spetner
Aaron J Spetner

Reputation: 2155

I like to try to keep column names unique throughout my database. I do that by using prefixes for tables and columns so that even foreign keys are uniquely named. This makes doing joins simpler as I (usually) don't need to reference the tables in the joins:

--------------------
 pe_people
--------------------
  pe_personID (PK)
  pe_firstName
  pe_lastName
  pe_timeStamp
--------------------

--------------------
 ac_accounts
--------------------
  ac_accountID (PK)
  ac_personID (FK)
  ac_accountName
  ac_accountBalance
--------------------

SELECT pe_firstName, pe_lastName, pe_accountName, pe_accountBalance
FROM pe_people
INNER JOIN ac_accounts ON (ac_personID = pe_personID)
WHERE pe_timeStamp > '2016-01-01';

Upvotes: 1

Dave S
Dave S

Reputation: 775

In my experience the more standard naming convention for columns is not to include the table name for the following reasons:

  1. It is unnecessary repetition
  2. It is harder to maintain because if the table name changes in the future all the columns would need renaming.
  3. The convention may not be clear to another implementer further down the line
  4. When you perform a query you can always alias the columns at that point if you need the table name in them.

I would only use the table name in a column if the column is a foreign key to another table. If you use this convention it makes it relatively easy to identify the foreign key columns within a table without the use of a relational diagram.

Upvotes: 4

Steve
Steve

Reputation: 216303

Your first example is better in my opinion. (The second one is confusing when you build queries and often requires the AS sql keyword)

However in my shop we use a little different convention.
PrimaryKey - this column should be named starting with ID and followed by tablename (IDPerson)
ForeingKey - this columns should be named starting with ID and followed by the name of the external table (IDDepartment)
OtherColumns - they should have a meaningful name for the data contained. It's required to repeat the tablename only for those fields that will happen to have the same name in different tables.

When using parameters to call stored procedure you should reverse the convention (@personID, @departmentID)

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280340

I don't like either example. I would prefer:

Person Table
 - PersonID     -- not ID, since this is likely to be referenced in other tables
 - FirstName    -- why is a first name just a "name"?
 - LastName     -- why not use the form more common than surname?
 - ModifiedDate -- what is a "persontimestamp"?

I am adamantly against primary keys, which will occur in other tables in the model, to be named with generic things like "ID" - I don't ever want to see ON p.ID = SomeOtherTable.PersonID - an entity that is common to more than one table in the model should be named consistently throughout the model. Other aspects like FirstName belong only to that table - even if another table has a FirstName, it's not the same "entity" so to speak. And even if you ever have joins between these two tables for whatever reason, you're always going to be differentiating between them as Person.FirstName and Customer.FirstName - so adding the Person or Customer prefix is just redundant and annoying for anyone who has to write such a query.

Also Timestamp is a horrible name for a column (or as a column suffix) in a SQL Server model because TIMESTAMP is a data type that has nothing to do with date or time, and the name might imply other usage to your peers.

Of course all of this is quite subjective. It's like asking 100 people what car you should drive. You're going to get a handful of answers, and now it'll be up to you to wade through the crap and figure out what makes sense for you, your team and your environment. :-)

Upvotes: 9

Angelo Badellino
Angelo Badellino

Reputation: 2191

I never see same rule between differente vendor. I do not know if there is some standard, but I think no!

Personally, I like your second option. Repeate the table name in the column name, is an unnecessary repetition.

Upvotes: 1

Control Freak
Control Freak

Reputation: 13233

Uggghhh, I guess i'm just too lazy so i would do Department.ID instead of Department.DepartmentID. I have enough redundant work enough as it is.

Upvotes: 2

Kakashi
Kakashi

Reputation: 355

Some columns might be repeated or have the same value, I generally use the same name in that case for timestamps etc. I work with financial data and would like to name the columns as the stocks name itself, rather than giving different name in different tables.

Upvotes: 1

Related Questions