September 16, 2010 at 5:51 am
A developer wants to name columns with prefixes indicating the table the
columns belong to. ex: In a 'Locations' table, he wants LocAddress,
LocCity, LocState, etc. I hate that. That seems completely un-necessary to
me. I can't imagine ever not knowing what table your data is coming from.
Do I really care what he names the columns? Are there any advantages to
naming columns like that? Do other developers ever really do that?
Opinions please!
I was looking for some way to explain my position and saw more than one recommendation (books & web) to use prefixes. Here's one plus a rule from Joe against it:
"SQL Queries for Mere Mortals" by Hernandez & Viescas:
pg 23 (early charter on design) - "be sure that you don't use the same field
name in several tables. ... The answer is simple: add a short prefix to each
of the field names. For example VendCity in the Vendors table, CustCity in
the Customers table, ..."
Foreword, by Joe Celko: "SQL Queries for Mere Motals is an excellent
introduction ..."
Joe Celko, "Stairway to Database Design - Step 1: Data Elements",
http://www.SQLServerCentral.com :
"the basic rule is that a data element name tells us what it is. The name
does NOT tell us about:
1. Its location in the schema with a table name. ....."
September 16, 2010 at 5:57 am
I wouldn't do it like that, a column name should reflect whats in the column there is no need for prefixes..
I would follow Celko's advice.
September 16, 2010 at 9:01 am
A old friend of mine used to say that there are as many naming conventions in the world as developers, DBAs and DAs are.
I do remember that particular naming convention was in use about 25 years ago in the age of the "network" DBMSs.
Personally I do not like to prefix a column name with a table indentificator, that would create "duplicates" in my data dictionary. first_name means exactly that no matter in which table is located.
So, in short, I'm in agreement with you guys.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 16, 2010 at 9:06 am
I also disagree with this - completely unnecessary. Prefixing tables with 't' or 'tbl' and stored procs with 'sp'..sure. Columns by their definition are already inside the context of the table, which is inside the database. You don't need to duplicate that metadata.
September 16, 2010 at 9:10 am
Tom Williams-229064 (9/16/2010)
A developer wants to name columns with prefixes indicating the table thecolumns belong to. ex: In a 'Locations' table, he wants LocAddress,
LocCity, LocState, etc. I hate that. That seems completely un-necessary to
me. I can't imagine ever not knowing what table your data is coming from.
Do I really care what he names the columns? Are there any advantages to
naming columns like that? Do other developers ever really do that?
Opinions please!
I was looking for some way to explain my position and saw more than one recommendation (books & web) to use prefixes. Here's one plus a rule from Joe against it:
"SQL Queries for Mere Mortals" by Hernandez & Viescas:
pg 23 (early charter on design) - "be sure that you don't use the same field
name in several tables. ... The answer is simple: add a short prefix to each
of the field names. For example VendCity in the Vendors table, CustCity in
the Customers table, ..."
Foreword, by Joe Celko: "SQL Queries for Mere Motals is an excellent
introduction ..."
Joe Celko, "Stairway to Database Design - Step 1: Data Elements",
http://www.SQLServerCentral.com :
"the basic rule is that a data element name tells us what it is. The name
does NOT tell us about:
1. Its location in the schema with a table name. ....."
Seriously, what's gained from that?
If you do a select from a single table you already know where you are. If you use more than 1 table, best practices recommend that you prefix all columns with a table alias... which you'll still have to do even with that method.
I'd just see those prefixes as noise more than anything else. But as bad practices go it's better than naming your table i20500a, i20500b (yup real live vendor system which I now need to plug into).
Other than visual noise, more typing and no real gain of any kind it's not that bad ;-).
September 16, 2010 at 9:16 am
Everything has a particular context that is already known, and adding entity prefixes to columns is redundant and nonflexible with the possible exception of Identity/GUID/Surrogate key columns.
Consider the scenario where you decide that the table name must change. Column prefixes add an unatural dependency, and now must also be changed.
However, there are articles and DBA's doing it both ways, so it might be more important to define a standard and stick with it. If the standard has not yet been defined then I would say it is more important what you think and want the standard to be then any one developer working on a specific thing.
September 16, 2010 at 9:51 am
Derrick Smith (9/16/2010)
I also disagree with this - completely unnecessary. Prefixing tables with 't' or 'tbl' and stored procs with 'sp'..sure.
I don't really want to start 'another' naming standards debate, however I have never seen the point of adding prefixes to table names etc..
It just adds more noise to the database, messes up the data dictionary and causes problems with intellesence. Also violates the 'rule' about not putting meta-data in object names and causes issues when you want to change a table into a view and vice-versa
September 16, 2010 at 10:17 am
steveb. (9/16/2010)
Derrick Smith (9/16/2010)
I also disagree with this - completely unnecessary. Prefixing tables with 't' or 'tbl' and stored procs with 'sp'..sure.I don't really want to start 'another' naming standards debate, however I have never seen the point of adding prefixes to table names etc..
It just adds more noise to the database, messes up the data dictionary and causes problems with intellesence. Also violates the 'rule' about not putting meta-data in object names and causes issues when you want to change a table into a view and vice-versa
The only prefixes I see really usefull are on views and table functions where you need to differentiate between the 3 types of objects for when you need to edit then. But then again you'll need to use () on functions so even that one is a tad bit overkill.
Prefixes on SP can be usefull if you also use extended procs, but again that's the only small exception I see.
September 16, 2010 at 10:17 am
I'm with the "leaving out the prefix" side. To me it's a waste of typing and reading.
If I have Customer.Address and Employee.Address, I need to qualify them in a join, but I'd do that anyway. In terms of the result column names, you can add "Customer.Address as "CustomerAddress" if needed.
September 16, 2010 at 10:48 am
Ninja's_RGR'us (9/16/2010)
The only prefixes I see really usefull are on views and table functions where you need to differentiate between the 3 types of objects for when you need to edit then.
Agreed but I would like to make a note, I do use table prefixes in DWH environments so to clealry identify FACT, DIM, STG, etc tables - in this case I would name my dimension "dates" table as DIM_DATES so to identify both functionality and content.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 16, 2010 at 10:58 am
PaulB-TheOneAndOnly (9/16/2010)
Ninja's_RGR'us (9/16/2010)
The only prefixes I see really usefull are on views and table functions where you need to differentiate between the 3 types of objects for when you need to edit then.Agreed but I would like to make a note, I do use table prefixes in DWH environments so to clealry identify FACT, DIM, STG, etc tables - in this case I would name my dimension "dates" table as DIM_DATES so to identify both functionality and content.
Excellent point. However I'd never consider those prefixes noise in queries. All those table "types" have very distinct reasons for being other than just saving data, almost like different object types.
September 16, 2010 at 11:04 am
Ninja's_RGR'us (9/16/2010)
PaulB-TheOneAndOnly (9/16/2010)
Ninja's_RGR'us (9/16/2010)
The only prefixes I see really usefull are on views and table functions where you need to differentiate between the 3 types of objects for when you need to edit then.Agreed but I would like to make a note, I do use table prefixes in DWH environments so to clealry identify FACT, DIM, STG, etc tables - in this case I would name my dimension "dates" table as DIM_DATES so to identify both functionality and content.
Excellent point. However I'd never consider those prefixes noise in queries. All those table "types" have very distinct reasons for being other than just saving data, almost like different object types.
I see we are in the same page - as it happens most of the time 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply