SQLServerCentral / Discuss Content Posted by Michael Lato / Article Discussions / Article Discussions by Author / Practical Methods: Naming Conventions / Latest Posts

RE: Practical Methods: Naming Conventions

Todd M. Owens — Fri, 12 Oct 2012 10:29:43 GMT

archived link.http://web.archive.org/web/20050723022626/http://www.dbazine.com/db2/db2-disarticles/gulutzan5

RE: Practical Methods: Naming Conventions

SQLNightOwl — Fri, 29 Feb 2008 22:19:33 GMT

I agree with spelling things out but couldn't disagree more with prefixes. Love schema's for grouping Sales.Orders, Sales.OrderItem. For the folks that love a tName or vName let me ask on thing? Do you apply the same thing to your columns? After all these are types of objects as well. I worked on systems that contained very elaborate prefixes. There is a wbocspuCompany for web based object Company stored procedure update and yes there was an i (insert), d (delete), s (select). The vendor carried this into the column names as well. It is a huge pain in the a$$.It's handy to be able distinguish between tables and views in code but I found it most useful to apply an _v at the end of the name. This gives me Customer table (nice, clean, simple) and related views Customer_v and Customer_vTop100 and Customer_vFormer. Applying an action at the end of stored procedure names allows the sprocs based on a single object to be grouped together. I'll have Customer_del, Customer_get, Customer_upd, Customer_ins, (or Customer_sav for insert/update). If I use a prefix then I have all my del[ete] procedures together, etc. I guess that may be handy if I'm trying to modify all my delete procedures. But normally I'm making a change to a Customer table and want to change all of the code related to those items. Give me a moderate size database with 100 tables and each Customer object will have I'll bet after reading this post you were able to figure out what the objects above do. After 15+ years of dealing with both prefixes or suffixes -- IMHO, suffixes and Object_Action names are the only way to go.Remember, it's an opinion and it may be worth exactly what you paid for it.

RE: Practical Methods: Naming Conventions

DBDeveloper — Fri, 29 Feb 2008 10:24:01 GMT

I strongly agree here. Use plain English whenever possible and most items should be immediately apparent even to new users/programmers. Lowercase "t" and "v" are pointless when you consider that the SQL Server management tools instantly show what is what, not to mention the fact that if you are working on a database in any way you should already be aware of it's contents. Some exceptions have to be made to avoid reserved words, but this does not rise to level of needing to prefix everything.

RE: Practical Methods: Naming Conventions

Jeff Moden — Fri, 29 Feb 2008 08:20:42 GMT

So, the young couple took the new born twins, a boy and a girl, to the God Father. "God Father", said the young Mother... "We would be honored if you named our children".The God Father thought for a minute and said "We'll call the girl 'Denise'." "Oh!", said the young Mother. "What a beautiful name for a girl. What shall we call the boy?"The God Father thought for a minute more, then cracked a smile from ear to ear, and raising his hand with index finger pointed as if having a revelation, proudly announced..............."DeNephew!"Be careful when choosing a naming convention... it may be something that you have to live with! :D

RE: Practical Methods: Naming Conventions

TraderSam — Fri, 29 Feb 2008 07:21:45 GMT

Although this topic is almost a year old (at this writing), I'll throw in one of my pet peeves...Don't abbreviate anything by omitting one or two letters.Emplyee for EmployeeAddrss for AddressManagmnt for ManagementEmplyeeAddrss for EmployeeAddressThis serves no purpose because eliminating 1 or 2 letters isn't shortening it enough to make any difference, and these are not common abbreviations. Plus, most people will always type those names out in a query wrong (we tend to read over the missing letters and subconsciously fill them in), so writing stored procs and queries against these object names is a pain because you spend more time going back to check "what was that name again?" each time you get an 'invalid object' error.Abbreviations are helpful if you have a lot of objects that contain the same word. For example...Management. A nice long word that could be used in a bunch of object names. ManagementObjective, ManagementHierarchy...which makes the names even longer. In this case, how about a well-accepted abbreviation for Management, such as 'Mgt' or 'Mgmt' (but be consistent, don't use both), so your objects become MgtObjective, MgtHierarchy. This works really well when you have several objects pertaining to Management, it makes it easier to read the names, and they all group together in an alpha-ordered list.Just use a little common sense...just a little. :)

RE: Practical Methods: Naming Conventions

peterjroy — Fri, 06 Jul 2007 09:45:00 GMT

A number of people have mentioned singular & plural when naming a table, as Mr.Celko said - it's a set, hence plural makes sense.

To be slightly obtuse here, use of t or v or pk & fk in naming is useful to someone such as myself who has moved between many systems. These simple conventions shorten the amount of time it takes to get aquainted with a new system. Purity of design versus practicality is something you should ask the guy paying you about, he will always opt for the cheapest pragmatic solution. So why don't you all try that approach.

RE: Practical Methods: Naming Conventions

rockmoose — Sun, 11 Mar 2007 11:57:00 GMT

table [user], table [transaction]ortable tuser, table ttransactionthe t prefix helps when it comes to some common reserved words.I don't mind using t and v as prefixes on tables and views, when it comes to partitioned views the t prefix can be used,since logically the partitioned view has the physical role of a table.I also find it good to be able to easily distinguish between the 2 different objects (t+v) in code.2 cents on why I like t + v prefixes on t+v database objects.rockmoose

RE: Practical Methods: Naming Conventions

Dave-148053 — Fri, 09 Mar 2007 11:09:00 GMT

No underscores for me, either. Fat fingers.

"If it was hard to write, it should be hard to read, and even harder to maintain."

RE: Practical Methods: Naming Conventions

Mike C — Fri, 09 Mar 2007 11:06:00 GMT

Hi EM,

One of the issues noted with prefixes is that the type of object can actually be changed. For instance, you might have a table named "tSales" or even "t_Sales" to use your recommendation. What happens when you change "t_Sales" to a View? You now have to change the name to "vw_Sales" on the server, and you have to change all client code that used to query the "t_Sales" table.

If the justification for using prefixes like "t_" and "vw_", etc., is to make it easier on the eyes in Enterprise Manager, this should not be a problem in SQL 2005. You can prefix all your objects with a schema (other than "dbo.") in SQL 2005, so all your "Sales"-related tables (for instance) will group together nicely (and separately from "dbo.") in SSMS without the need for redundant prefixes in your naming convention.

To use your example, you could group all the Sales-related tables into a "Sales" schema on SQL 2005:

Sales.SalesSales.HistorySales.CategoriesSales.RepsSales.Contacts

Same goes for SP's and UDF's. You can use the simple "ObjectAction" naming convention and assign them to the proper respective schemas.

RE: Practical Methods: Naming Conventions

ZETM — Fri, 09 Mar 2007 10:08:00 GMT

I agree with a lot stated here. Couple of comments from my general experience: Not sure I necessarily agree with prefixing objects with t for table, v for view. From experience, this can lead to eye fatigue. I like to add underscore so the intended table functionality can quickly be scanned. t_Though you might say tCustomer and tVendors is easily distinguishable, compare that to t_Customers, t_Vendors. I at least feel the prefix still allows quick discernment of a table object, but I found the underscore promotes less eye fatigue with objects. Regarding indexes: I usually prefix cidx for clustered, idx for non-clustered in front of the index name. That way I can quickly scan an index list and see what I'm clustering on. Regarding stored proceudres/views: vw_asp_Again the underscore allows better readibility of the view/proc name. I prefer asp as a prefix namely because procs list ahead of the systemsp_. I guess p would work as well; however, the difference betweenp and sp, though trivial, is very close in sorting and placement in alphabet. I too at one time used variations of prefixes for procedures and discovered 3 letter prefix _ worked best and the a catches the eyewhereas the e can get lazy with a p sp combination. I settled onasp_ as a preference because 'a' just jumps out ahead of 'p','s'.Again these are just some personal preferences from working with large DBs of mine. We also generate a lot of temp and lookup tables in our environment. We us lu for lookup with these tables. In regards to : Use mixed case rather than underscores (in most cases) to indicate word breaks . Use "pCustomerAddressCreate" instead of "pcustomer_address_create". Not sure I agree with that either for same reasons as listed above, but to each hisown I guess. Of course avoid usage of company names when you working as a contractor across several companies doing work. SalesSalesHistorySalesCategoriesSalesRepsSalesContacts...ad infinitum....WHile this may help you as a hired contractor keep your clients code straight, this methodology drives internal contractors crazy.

RE: Practical Methods: Naming Conventions

Lyle Fernquist — Fri, 09 Mar 2007 09:15:00 GMT

London, sign me up if you'd like a DBA with 3 decades of experience who loves to sip a pint and watch football at the corner pub.

RE: Practical Methods: Naming Conventions

Ninja's_RGR'us — Fri, 09 Mar 2007 08:07:00 GMT

Yup, but I have very strict requests. Never hurt to ask however.

RE: Practical Methods: Naming Conventions

RichB — Fri, 09 Mar 2007 01:14:00 GMT

anyone from the objectVerb, no prefix school looking for a job in london?

RE: Practical Methods: Naming Conventions

Dave-148053 — Thu, 08 Mar 2007 14:23:00 GMT

I vote thumbs down on prefixes. I am now dealing with 400+ tables that all begin with TBL_XX_tablename, where XX is the initials of the guy who created it. The idea is if you have any questions about the table, you can wander over to the Creator's cube and ask him (we have a rich oral tradition here). Besides, if you can't tell its a table from reading the code, perhaps you should be in IT management...

I do practice the object-verb naming convention. If you have a stored proc that adds a customer, call it CustomerAdd. If you do it the otherway around, you have a bunch of procs that all line up in the treeview like

AddCustomer

AddThat

AddThis

when what you want is

CustomerAdd

CustomerDelete

CustomerUpdate.

RE: Practical Methods: Naming Conventions

RichB — Thu, 08 Mar 2007 08:09:00 GMT

Aye, I agree with Eddie, can't stand these daft prefixes people get taught at university/polly.

Plural/Singular it doesn't really matter that much, as long as it scans well. Closer you get to natural english the better.

Steve's point about object prefix is absolutely spot on. When you get developers who insist on insert_something, get_something, add_something else its a pain in the jacksy.

They say 'oh but we know its insertx' but is it? it might be 'addx' or 'newx' or gok whatever someone was thinking at the time.

When you need to review the code you mind numbingly scroll up and down 100's of get_x procs. The whole verb_Object construct is just not fun. Object_verb is so much more usable.

Another thing that gets me is when developers assume that the column is always a part of the table, and you always reference it as such.

create table SendMoney(from, to, reason, time)

create table DispatchRider(from, to, reason, time)

So now from can mean several things. of course you wouldn't use From anyway, would you. What with it being a reserved word and all.

RE: Practical Methods: Naming Conventions

Lyle Fernquist — Thu, 08 Mar 2007 08:08:00 GMT

"You can use whatever you want as long as you don't work for my company, but I do not see a reason for referring to a customer name as customers.name "

Spoken like a true COBOL warrior. Who needs set processing?

RE: Practical Methods: Naming Conventions

Eddie Wuerch — Thu, 08 Mar 2007 01:09:00 GMT

Just echoing other comments opposed to hard-coding an objects type in the object's name ("t", "tbl", "v", "viw", "int" - prefix or suffix): it is a blueprint for disaster. As databases and applications grow and wane, tuning and other application factors change tables to views (partitioned views, tables moved to external databases and referenced through views, etc.), views become tables, and columns change datatypes (int -> bigint, char -> nvarchar). Many database folks working with huge fluid systems shudder at Hungarian notation and all that follows. It's only a matter of time before they have 50+ views that all begin with 'tbl'.

-Eddie

RE: Practical Methods: Naming Conventions

Alexander G. — Thu, 08 Mar 2007 00:52:00 GMT

Once again to the singular vs. plural topic: Of course there are valid arguments for using plural.

But most of them are weak especially the one that says: "An employee table contains employees so let us name it employees!". A table can contain less than 2 rows, do you want to rename it?

You can use whatever you want as long as you don't work for my company, but I do not see a reason for referring to a customer name as customers.name

And please don't counter like: "Customers is genitive without the apostrophe."

RE: Practical Methods: Naming Conventions

Ninja's_RGR'us — Wed, 07 Mar 2007 11:27:00 GMT

Care to elaborate a bit more on this one?

RE: Practical Methods: Naming Conventions

noeld — Wed, 07 Mar 2007 11:23:00 GMT

One of the things I have come across is that *not* prefixing or suffixing tables AND views may save the day on databases with large tables.You maybe wondering "why?". Well you could change a table into a partitioned view ( with no client side changes ) or change a view into a table for materialization purposes ( again with no client-side changes) These scenarios are not that uncommon and is something you may want to keep in mind.Cheers,

RE: Practical Methods: Naming Conventions

Mike C — Wed, 07 Mar 2007 11:14:00 GMT

I think Steve hit the nail on the head with why people get into using prefixes. A lot of people got used to naming their database objects with prefixes precisely because they "group together" well in Enterprise Manager and other GUI tools, so they could easily group user-defined objects for easier management.

OTOH, with the addition of schema support in SQL 2005, I think the rationale/justification people use for built-in prefixes is gone. Schema support in SQL 2005 and how it affects naming conventions would be a nice addition to this article.

RE: Practical Methods: Naming Conventions

Steve Jones - SSC Editor — Wed, 07 Mar 2007 09:07:00 GMT

I tend to favor using suffixes if you want to distinguish views from tables from procs, etc. The big reason is that I want all things affecting a particular area, i.e. Employees, to be grouped together when I'm looking at the objects. So table Employees is next to views, EmployeeSalary_v, EmployeesTerminated_v, etc. Also uspEmployeeGetOne, uspEmployeeGetAll is easier for me to find in QA than uspGetEmployees, uspGetAllEmployees, which may be separated by many other objects in a list.It always gets weird and hard when you start combining multiple objects into one function, but I think you need to do the best you can.It's more important to be consistent than to worry about which standard. Within days, you'll pick up any standard and get used to it. But if things are haphazardly named, it's way harder than if every table has a t prefixed.

RE: Practical Methods: Naming Conventions

SQLNightOwl — Wed, 07 Mar 2007 08:39:00 GMT

What a can of worms. For what it's worth here's another opinion. Remember it's worth what you paid for it:

Prefixes - as a general rule I avoid them like the plague unless they server a very specific and limited purpose.

Tables - no to any prefix unless it's an enterprise wide database and then the prefix should be a "department" name or mnemoninc (hr, sls, mkt, eng, prd). Prefixing a table with t is the same idea the author discourages when refering to data types in column names. After all isn't a table the data type for a table? Tables should be singular nouns. If a table of Employee records is plural (Employees) then then the follow on logic is that the wrapper CRUD procedures would be CreateEmployees, ReadEmployees, UpdateEmployees and DeleteEmployees. What if I only want one? The name may make me wonder if it only creates multiples.

Views - one of the two places where I really like adding a type identifier (suffix) of _v or _vw. Views are special case virtual tables and updateability rules will vary. If I'm messing with a view in procedural code I want something that smacks me on the head to let me know I'm working with one.

Procedures - See rule number. In a moderately complex database you would end up with all Employee related procedures scattered all over the browser window. Change to a suffix and magic happens because you then end up with Employee_get, Employee_ins, Employee_upd and Employee_upd. Not really rocket science to find or figur out what those do.

Functions - the other place I like the type identifier and this time it's (gasp) a prefix (ufn_). This is to let me know it's a user function. Again this one smacks me on the head and lets me know I'm working with a user functoin and not a built in one. ufn_Split(), ufn_LastDayOfMonth(), ufn_ZeroPad().

Relationships - I head to the OO world here with ChildTable_has_ParentTable. It really helps out.

Indexs/Keys - Primary: PK_TableName or TableName_PK; Unique Key: UK or AK and TableName -- If I've got more than one I reevaluate my database design. Foreign Key (index) FK and the ReferencedTableName. Wheter you use a prefix or suffix is not relevant to me as I don't "code" against them -- just be consistent.

One place where the author and I do agree is on the need for being consistent. I hate to alias anything that's used by another program (columns and parameters) so just don't go there.

And remember; opinions are like aXXholes, everyone has one and they're all full of it.

RE: Practical Methods: Naming Conventions

Todd M. Owens — Wed, 07 Mar 2007 08:36:00 GMT

I've gone back back and forth on the singular v. plural table names argument. In the early days when I was a systems analyst, I was taught that table names should be singular because entity type names where named singularly. Well I changed my mind in the last 2 or 3 years because a table is collection of instances, and it makes more sense to refer to a collection in the plural.

As for the "we don't do it that way with OO" argument, I must admit surprise that someone would try project an object oriented programming standard onto a relational database object.

For good discussion on general SQL naming conventions, including plurals see: http://www.dbazine.com/db2/db2-disarticles/gulutzan5

Regard all - Todd

RE: Practical Methods: Naming Conventions

Lyle Fernquist — Wed, 07 Mar 2007 08:28:00 GMT

I appreciate the fact that universities have been teaching the singular concept for years. It is a topic that I have debated with folks like Ted Codd and Chris Date for decades. I also love the controversy bred by this topic.

A roomful of human entities is not a gathering of person, but people. A company meeting is not attended by employee, but employees. Reinventing the language "wheel" is an unnecessary expense that leads to higher maintenance costs, longer development times, and overall confusion.

One of my first mentors was Peter Page', the inventor of NATURAL language. His intent was that the language of programming read like a great book. Have you read any great books lately?

RE: Practical Methods: Naming Conventions

Steve S. Yang — Wed, 07 Mar 2007 08:06:00 GMT

the article intended to cover naming conventions but i found its names quite uncommonly named. name employee table 'tEmployee'? give me a break.

RE: Practical Methods: Naming Conventions

Alexander G. — Wed, 07 Mar 2007 07:59:00 GMT

Lyle wrote: "A table that contains information about employees becomes the Employees table, a noun. A table is a collection similar entities, plural. "

I strongly vote for singular, because (OO) classes and tables are both but entities, and no java programmer would ever name a class plural. Accordingly a table should be named as an instance of the entity i. e. singular.

RE: Practical Methods: Naming Conventions

Todd M. Owens — Wed, 07 Mar 2007 07:41:00 GMT

While I agree with the essence of the previous posts, saying something is "nonsense" without constructively suggesting alternatives or even saying why is not helpful.

As for a statement like "never use abbreviations or acronyms", well ne dis jamais jamias (never say never). As long as they are used sparingly and consistently I don't believe there will be problems.

I was dismayed to see the word "Data" included in one of the database name samples. Terms like "Data" and "Information" should rarely if ever appear, as they add no descriptive value. Was the db for an OLTP/TPS or a Mart? Well then say so in the name.

Your backup files suggestion is fair, and I have seen variations commonly used. Although one could argue where environments keep the date and time as displayable/sortable file metadata, why bother with putting it in the file name?

In my experience, prefixing tables with a "t" (and views with anything) is unnecessary and counter productive. For the most part, when doing development or maintenance, the person on the job always has the context of the object readily available, if not in the forefront of thoughts. In any event SQL Server will easily tell you whether an object is a view or table or whatever. Even when writing queries an objects "tableness" or "viewness" is not relevant. Tables will have simple, short names when named correctly. I believe they should be named in the plural (e.g., Users), although I keep entity-type names in data models singular. View names tend to longer than table names because they should also describe the view's purpose with an allusion to whats in the where clause and/or whether its a UNION or OUTER JOIN and so on.

In column names I think classwords add value. I think "ID" should be used anywhere it makes sense (e.g., UserID), but always used as the classword for surrogate keys. PhoneNumber is a much better name than Phone, as the Number classword means fixedlength string primarily comprised of digits. In my opinion, using prepositions like on and by should be avoided; classwords tend to make the column name clearer, as in CreateDate, CreateUserID.

I also think use of prefixes on columns indicating "indexness" or "keyness" is pointless and serves to obfuscate.

I could go on with more suggestions, but I've spent enough time on this already. I hope my suggestions will give the author and other interested parties something to consider.

Regards all - Todd

RE: Practical Methods: Naming Conventions

Lyle Fernquist — Wed, 07 Mar 2007 06:28:00 GMT

I advocate the use of English for naming standards. Never use abbreviations or acronyms. If the word can't be found on dictionary.com then don't use it. No prefixes or suffixes are necessary, just apply the rules of English that we all learn early in life.

A table that contains information about employees becomes the Employees table, a noun. A table is a collection similar entities, plural.

The stored procedure that inserts an employee to the Employees table is named AddEmployee.

Stored procedures perform actions considered verbs.

Wen the enterprise vocabulary becomes English the maintenance cost goes down. You write a program or system once and maintain it forever.

RE: Practical Methods: Naming Conventions

Jim Russell-390299 — Wed, 07 Mar 2007 05:01:00 GMT

I stopped reading as soon as I saw the recommendation to use Hungarian notation ("t" prefix for tables, etc.) I wish that foolishness had never been started!

RE: Practical Methods: Naming Conventions

Sean Fackrell — Wed, 07 Mar 2007 04:55:00 GMT

I agree with most of what is presented in this article. You can go over the top with naming conventions but this is about right IMHO.

The compound key constraint naming is the exception to the rule as noted but that shouldn't stop you following this convention or a slight variation of this convention.

Generally naming a fk constraint <table name>_<constrained col name> will work OK if your pk table uses <table name>ID as its primary key column - it's then obvious which is the pk and which the fk. I prefer qualifying constraints with _FK/_PK/_DF suffixes rather than prefixes. This means you can list sysobjects by name and objects belonging to the same parent are listed together.

I opt for no prefix for tables (and just avoid reserved key words) a vw- prefix for views and use v- to distinguish validation tables from data tables, also using a z- prefix for tables that contain system data rather than user data. E.g. Customer, vSalutation, zVersion.

RE: Practical Methods: Naming Conventions

Fozzie — Wed, 07 Mar 2007 02:26:00 GMT

tCustomer_CustomerID
tInvoice_CustomerID

Hmmm... so which one is the foreign key and which one is the primary key? Or more importantly which table is the foreign key referencing?

I tend to use pk_<tableName> and fk_<thisTableName>_<referrencingTableName>. If you wanted to know the columns being referrenced then the information is within the key.

What would happen with a composite unique key made up of three columns? I'd just do this:

uk_<tablename>

RE: Practical Methods: Naming Conventions

Alexander G. — Wed, 07 Mar 2007 01:31:00 GMT

I don't agree with the naming conventions of identifying columns (<tablename>ID). That does not work with compound identifiers. First, you need more than the tablename, second, "ID" suggests that this column is an unique identifier.

I prefer using a PK (Primary Key) suffix or prefix.

RE: Practical Methods: Naming Conventions

Wayne Laubscher — Tue, 06 Mar 2007 23:10:00 GMT

To the point. A doc that DBA's and people in the design phase should commit to memory from day 1. Note .... going down the wrong road and trying to get back to this point is not fun though!

Practical Methods: Naming Conventions

Michael Lato — Wed, 28 Feb 2007 07:33:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mlato/2895.asp