The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database object naming advice needed

We are having some debate on my team about database object naming. there are two camps.

One camp seems to favor Verb Noun style:

Another prefers Noun Verb:

Verb Noun seems to be more standard in other programming environments.

Does anyone have any strong reasons why not to use the Noun Verb style for Database objects like stored procedures and user defined functions?
BigJimSlade Send private email
Friday, May 23, 2008
I strongly prefer verb/adjective noun format. But I've had this same discussion with other developers and it simply comes down to preference. I would prefer to call something CustomerPhone instead of PhoneCustomer. Just like I would never say "hey, that is a cool car blue" in regular conversation.
Friday, May 23, 2008
Neither approach is arbitrary, and to make a decision on an approach it's a good idea to examine why it exists.

The verb-noun approach is strong because it's in line with how English is spoken idiomatically.  If you need to get a record, you think "I need to get a record," and then "GetRecord" seems logical.

This means, though, that if you're looking at an alphabetized listing of your methods, you'll see them all grouped by verb.  And grouping them by noun is really more useful, because it's generally much more useful to know everything your program does to a type of object than it is to know every object that a verb acts on.

That said, I'd claw my eyes out if I had to use that approach.
Robert Rossney Send private email
Friday, May 23, 2008
Thanks Guys. The primary reason some folks are advocating the Noun-Verb approach is due to the sorting characteristics in management studio.

I do agree this is useful; but having to read and write code like this would devolve into some bizarre yoda speak.

Friday, May 23, 2008
It's not clear to me what a "database object" is.  I know, I know, everything is an object. That doesn't tell me anything.

Database tables may not fit in well with common objects as far as naming conventions go.  In particular,  "verb-noun" seems to go well with objects that are primarily invoked because of their actions.  Tables are primarily invoked because of the data they retain and present.  The difference is subtle, but real. 

Columns are even farther away from the "verb-noun" paradigm than tables are. 

prodecures and triggers are action oriented,  so I'd guess that verb-noun would work pretty well in your environment, if that's the way action oriented objects in programs are named. 

View naming is problematic.  If you look at the query inside a view,  it's action oriented.  But if you think of the view as a virtual table,  then views should probably be named the way tables are named. 

Bottom line:  there is no accounting for taste.
Walter Mitty Send private email
Friday, May 23, 2008
Entities should be named with noun phrases.

Actions should be named with verb phrases.

In a database, entities would be things like tables and views. So they'd be Customer, Order, OrderLine, etc. (Let's not get into whether they should be singular or plural).

SProcs and UDF's are actions - they do something. As such, they should be named with verb phrases, so GetOrdersByCustomer, UpdatedOrderLineQuantity, etc.

OrdersByCustomerGet just looks weird.
Chris Tavares Send private email
Friday, May 23, 2008
I think Walter and Chris nailed what I couldn't come up with which is: since sprocs and functions are action oriented, the correct way to name these types of objects are with verb noun phrases.

To change that because of a UI based sorting characteristic is short sighted and bad practice.

Thanks for all your responses
BigJimSlade Send private email
Friday, May 23, 2008
This discussion makes me wonder if it would be possible to build a tool that could sort the SPs, etc by the objects they relate to. I guess it's hard to know whether XCustomerYOrder is primarily a function on Customers by Order or on Orders by Customer - perhaps it could be listed in both places?
Gareth Send private email
Friday, May 23, 2008
The biggest problem is that database objects have a flat namespace.  Either way you name it doesn't matter when you only have a few -- but thousands of procedures later and you'll wish for some kind of structure!

How about a prefix?

Almost H. Anonymous Send private email
Friday, May 23, 2008
I would not use verbs in entity or table names. The reason being is you are working with data, not what you are doing with data. Same reason in CSS you are told not to define classes around format but function. If you do this, if you decide later to use the table or class in different ways, it breaks with the naming convention.

For example, if you use GetOrders, and later that table is used to store Orders related to Customers or PO's, etc, the name no longer fits. In CSS, if I have a header that is a title for blog entry, and it always has blue text, if I name the class ".bluetitle" and later decide to make it red, it now has a name that is irrelevant. Should use "bogtitle" instead. Make sense?

In our SQL naming conventions, we use an object-oriented database schema and our stored procs have verbs, like Orders_GetAll, etc. This means our tables need object-based nouns for names, like Companies or Customers, as they are related via object id's in relational tables elsewhere. This is much more flexible and allows yout to define tables witrh fields representing "property-like" members.

You can also use acronyms and prefixes, like datTableName, or mapTableName, or like in Microsoft Dynamics, where they use SYS005, and a legend that defines the purpose of the table, though thats less intuitive.
Friday, May 23, 2008
You could just consider to make a 'dummy' function for every function. Solves any dispute and it costs a whole lot less than endlessly debating over it. And it solves any sorting problems.

GetOrders {

OrdersGet {
  call GetOrders;
Marck Send private email
Saturday, May 24, 2008
> To change that because of a UI based sorting characteristic is short sighted and bad practice.

IF that sorted UI is how you (are forced to) view your stuff, it is actually a very pragmatic choice.

Monday, May 26, 2008
I always employ Noun-verb for sorting. For myself, the slight sacrifice in 'correctness' is worth it in terms of managing code. To my knowledge, there is no reasonable way to group sprocs other than alphabetically.


Having them spread willy-nilly throughout the list of sprocs in whatever tool you are using would hurt my brain too much.
Jason T
Monday, May 26, 2008
We name tables using nouns, and then sprocs & table functions by prefixing the tablename in front of the verb clause:


We then get the nice sort order, and still are able to read the action being performed in a readily understandable manner.
Monday, May 26, 2008
What naming convention do you use when your sproc touches multiple tables?
Chris Tavares Send private email
Monday, May 26, 2008
If its a big database with say hundreds of tables, I'd personally prefer the noun verb approach although it may not be linguistically pleasing. The same approach is also commonly used in inventory naming primarily for ease of sorting e.g. "BOLT, TEMPLEMANN, STAINLESS STEEL, HEAT MARKING NO: 2521, GREY, 4" x 5", BS4150" or "PENCIL, 2B, YELLOW, FABER CASTELL"
Monday, May 26, 2008
I find GetCustomers, InsertCustomer, DeleteCustomer more natural than CustomersGet, CustomerInsert, CustomerDelete.  The only reason to use the latter is for sorting purposes.

So the first question to ask is, is there another way to get the desired sorting?  (In SQL Server 2005 the sorting is by schema before name, so I *think* you can use schemas to enforce useful sorting; but there may be unpleasant side effects I'm not aware of.)
Kyralessa Send private email
Monday, May 26, 2008
I'll add my voice to those saying the 'logical' way to do it is verb-noun.

However, I have found that having a pseudo 'namespace' sorting of routines named noun_verb has been helpful.

Especially with getters/setters -- you wind up with thousands of "Get_xxxxxx" calls, which irritate me when I'm trying to figure out "Did we call it a 'stack'?  Or a 'Record'?  Or a 'stack_record'?  Or a 'record_stack'?"

And of course, usually I would never name something as generic as "stack" or "record", that's just for the example here.
Tuesday, May 27, 2008
>> What naming convention do you use when your sproc touches multiple tables? <<

We use whatever is considered the "main" table in the query.  This works fairly well, as we seldom query on association 'joiner' tables directly -- we're usually using them as a way to fetch some related data, and aren't the main focus of our query.
xampl Send private email
Wednesday, May 28, 2008
The only problem with naming something "getCustomerOrders" is that alphabetically, you'll end up with 100 gets in a row.  So when you start scanning the documentation, it is harder to read

The other main reason to use CustomerOrdersGet is that when you look at all objects together, they'll all be grouped together alphabetically:

I don't look at tables and procs at the same time, and I don't tend to look up gets and updates at the same time, so there is little reason to group them like that.

When I would want to group things together is when they all belong together.  In particular, I'm thinking about all the Java classes required to implement an EJB.  I tend to create them together and work on them together.
Lance Hampton Send private email
Wednesday, May 28, 2008
"What naming convention do you use when your sproc touches multiple tables?"

I will take the approach of using a 'logical' name for the sproc, rather than indicate the table sources.

In the Customer_* examples, 'customer' is a concept, even if the underlying table is called CUST or a collection of tables CUST, CUST_DET, or some other legacy stuff. Sometimes you get stuck with someone else's naming.

I could have a sproc called CustomerCreditHistoryGet, which returns 15 datasets which I use in a display screen and alternatively in a report, which could conceivably touch 37 tables and have multiple joins.

For distributed apps, I wrap these sprocs in web services, which also use a logical naming.

My philosophy of sproc design is that the underlying data table storage can change, but the logical return value is what is important to the consumer of the returned data.

If you don't implement multiple result sets and you work with sprocs like:


there could be 5 or 6 underlying tables joined in.

As I see it, stored procs should (to a degree) provide a layer of abstraction, just as class interfaces do, just as web services can.
Jason T
Thursday, June 19, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz