Need to ask an experienced designer if we need to make the field names in the database unique? - MS SQL

So no two tables have fields named same with in the database project?

In the past I have had a lot of trouble when field names were same specially in paging when two pages were combined by a join with similar name field.

What do you think?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No, there is no solid need for unique column names, and I highly recommend against it.
More intuitive if every table has a primary key named id (or whatever floats your boat)
More intuitive if foreign keys have the same name.  I've seen some places that had a members table, with a primary key of id, and sloppy developers named foreign keys in different tables member_id, account_id, patient_id, member_name_id, etc.  Big nightmare to maintain.
For auditing columns such as created_by, created_dt, last_updated_by, last_updated_dt, it's easier coding to have the same names.
Many tables will have a column with a very specific meaning, such as active_yn, expiration_dt, start_dt, name, address, etc., and as long as the definitions are consistent there is no valid reason to force unique column names.

Also if you ever need to do anything via dynamic SQL, if the names aren't the same then you'll need a custom table with table names and column names, which is more overhead than needed.

>when field names were same specially in paging when two pages were combined by a join with similar name field.
Sounds like the previous developers did a poor job in design if two columns with the same name have different definitions.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dsackerContract ERP Admin/ConsultantCommented:
The best practice is to have field names the same, if they represent the same data. In other words, if you have an CustomerOrder table, the CustomerNumber should be named the same as the CustomerNumber that would be in a Customer table.

Otherwise, you can eventually have a readability and maintenance nightmare.

When you join tables, use aliases both with your tables (for ease of coding) AND with your field names. Here is an example:
SELECT  c.CustomerNumber AS Customer,
        co.CustomerOrder AS [Order Number]
FROM    Customer c WITH (NOLOCK)
INNER JOIN CustomerOrder co WITH (NOLOCK) ON co.CustomerNumber = c.CustomerNumber

Open in new window

You'll never have problems prefixing your fields names. And if you need to show the same field name from different tables twice on a web form, simply use the AS FieldName to distinguish them.
goodkAuthor Commented:
So if I have tables

1- Table=Names
IDNames, Name, Description,eMail

2- Table=Visits
IDVisits, IDNames, Description, CityName

Have a one to many on IDNames and use Alias for Description.

Would this be ok in all situations?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
As far as the table and column names, yes.

As far as writing a query where both table's .Description column is returned, then you'll want to alias one or both, such as NameDescription and VisitsDescription.   Something like...

SELECT n.IDNames, n.Name, n.Description as NameDescription, v.Description as VisitDescription
FROM Names n
   JOIN Visits v ON n.IDNames = v.IDNames

Open in new window

goodkAuthor Commented:
Thanks. Would this work in case I want to use pagination?
Scott PletcherSenior DBACommented:
There is absolutely no problem with multiple tables having the same column name or names.  

If you're still in the process of choosing column names, you're lucky.  Take advantage of that.  You should clearly define what different column suffixes mean and when to use them.  Such as, what does "xxxId" mean vs. "xxxNumber"?  Will you suffix date/datetime columns, and if so, with what?

What you decide should be reasonable, but after that, consistency is far more important than specific details of the naming methods you chose.

But avoid data type prefixes, such as "iXxx" for <integer>Xxx, and "dtXxx" for <date>Xxx.

Finally, avoid soft-delete flags unless absolutely required.  That hold-over flag from "master" files processing will kill SQL performance.  Instead, if you really need a similar capability, create a separate table of deleted rows and have a view that concats active and deleted rows for when you actually need that.
goodkAuthor Commented:
Thank you guys.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.