SqlServer naming convention

Question

I have a Widgets table and a Persons table

both contain an Identity (seed) column

What is the recommended naming convention for an identity column?

it " id " (for both tables):

 Widgets.Id Persons.Id 

or

 Widgets.WidgetId Persons.PersonId 

Are there any advantages / disadvantages in the first comparison with the last?

Answer 1

This is one case where I would go for redundancy and get Thing.ThingID
Otherwise, you should use it everywhere when you join

Note: this has already been done for death and above for .se programmers:
Why is the primary key column name of the Identifier table considered bad practice?

Answer 2

The best practice for this is to preface the table name.

This will greatly simplify when connecting to the field in which you refer in the SELECT list.

It also makes random bad JOIN conditions impossible, i.e.

JOIN TableA ON ID = TableA.ID

As a rule, duplicate field names in tables should be avoided if they do not represent identical data.

Answer 3

Using the table name in the identification column is a little redundant, but I find it useful and more visual, especially when executing foreign keys. For example, what seems better:

 SELECT P.PersonID, P.FullName, W.WidgetID, W.WidgetName FROM Widgets W JOIN PersonWidgets PW ON W.WidgetsID = PW.WidgetsID JOIN Person P ON P.PersonID = PW.PersonID 

OR

 SELECT P.ID as PersonID, P.FullName, W.ID as WidgetID, W.WidgetName FROM Widgets W JOIN PersonWidgets PW ON W.ID = PW.WidgetsID JOIN Person P ON P.ID = PW.PersonID 

The first query is more explicit and leads to less random joins. It also eliminates the need to use aliases in your SELECT list.