Column naming standard – general opinions

[gargoyle60]

Don't want to start a long-winded discussion, just seeking some
general views. . .

I was trained in SQL years ago and was encouraged to use lower-case
letters with words separated for emphasis using the underscore
character, for example:
customer_num, employee_id, invoice_line, transaction_code

I've stuck with this approach ever since, but now that I make use of
oo programming the general standard is to name oo instance variables
without underscores and capitalising all words except the first for
emphasis, such as:
customerNum, employeeId, invoiceLine, transactionCode

However, bridging the gap between relational databases and oo
programming means that when binding table column names to object
variable names, I really need to pick one or the other (you may
disagree but that's my aim).

So, when it comes to naming columns, what are most of you using?

[Erland Sommarskog]

All lowercase, using underscore only exceptionally. (When no underscore
would make the name to confusing.)

Look at it this way: if you use different conventions for columns
and class members, you can easily tell them apart.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

get a copy of SQL PROGRAMMING STYLE. I follow the ISO-11179 meta data
rules and I did research on readable code for AIRMICS. The lower-case
letters with underscores is right but there is more to it than just
that. The template is [<role>_]<attribute>_<property>.

[gargoyle60]

Thanks to all who reponded.

[bill]

Hi Joe,

I'm going to pick up a copy (liked your "trees" book btw, especially
the nested sets).

Question about the template:

If I understand, you'd do things like
ship_to_customer_nr
bill_to_cutomer_nr

Because it's relational, column order is not significant, but when
people look for stuff, it's nice to be able to sort the catalog.

If the naming template were attribute_role_property, the columns would
look like this:
customer_ship_to_nr
customer_bill_to_nr

It would be easy to find the columns when you issue this query:
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME = '<something>' ORDER BY COLUMN_NAME
because all the customer stuff would sort together.

What do you think?

Thanks,

Bill

[--CELKO--]

A>> If the naming template were attribute_role_property .. <<

that would split the base data element name and mess up the data
dictionary. Roles occur only when a data element appears in two roles
in one table so they are local

[bill]

That's a good point. It would be easy enough in the query to sort by
the "root" data element name if one used your suggested naming
convention, and I like the idea of not splitting the element. Your
convention also verbalizes more naturally (e.g. who says "Hey, give me
the customer, ship to e for that order"? They would more naturally
say "give me the ship to customer for that order.")

Question (waiting for Amazon to ship book):

1. Do you make all object names (tables, views, etc) lower case?
2.. When writing the SQL, do you put the rest of the sql syntax in
upper? e.g. SELECT column_nm FROM table_nm WHERE ....

Thanks,

Bill

[--CELKO--]

>> 1. Do you make all object names (tables, views, etc) lower case? <<

Capitalize schema objects, since they are usually proper nouns. Your
eye is trained to jump to an uppercase letter, so you can quickly tell
them from scalar data elements. Use a collective noun for tables
since they are sets (Employee = bad, Employees = better, Personnel =
best)

>> 2.. When writing the SQL, do you put the rest of the SQL syntax in upper? e.g. SELECT column_nm FROM table_nm WHERE .... <<

Yes. This is because of a visual effect called a Bouma. You read
the word as a single unit rather than letter by letter or in
syllables.

All of this is in the book in detail.