Should column names be unique across all tables?

Question

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.

Answer 1

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. :-)