davidandrews13 (11/15/2013)
i always felt that if the ID column was in a Table called 'State', then its self explanatory that its the 'StateID'.so it was unecessary to call the column 'StateID'.
It also makes it easier to see what is the Primary Key and what is the Foreign Key, in your query, where 'ID' is the Primary Key and [tablename]ID is the Foreign Key.
e.g
where t1.ID = t2.StateID
as opposed to:
where t1.StateID = t2.StateID
I don't like this for three reasons.
1) Ambiguity. ID by itself is meaningless. This is like having a column named Date. It gives no indication what it means. I realize that with ID it is kind of a clue but you have to stop and think about what it means.
2) (one of my biggest annoyances with some systems) is that a column name should NOT change its name based on usage. What I mean by that is we should not change the name of a piece of information just because it is in another table. This leads itself to things like StatePK in the States table being changed to StateFK as a foreign key.
3) The same named column meaning different things in different tables. For somebody new to your system they would look at your tables and very possibly write something like this:
where t1.ID = t2.ID
Notice how strange that becomes. It looks perfectly normal except we have no idea what ID means and why can't you join these two tables on columns with the exact same name? Because we have to add context to the names in order for this to work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/