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?
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.
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?
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.