SQL Server - Column naming standard – general opinions

Asked By gargoyle60 on 20-Nov-09 05:38 AM
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 have 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 is my aim).

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




Erland Sommarskog replied to gargoyle60 on 20-Nov-09 05:00 PM
gargoyle60 (gargoyle60@example.invalid) writes:

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, esquel@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-- replied to gargoyle60 on 21-Nov-09 08:40 AM
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 replied to gargoyle60 on 22-Nov-09 04:12 PM
Thanks to all who reponded.
bill replied to --CELKO-- on 22-Nov-09 10:51 PM
Hi Joe,

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

Question about the template:

If I understand, you would  do things like
ship_to_customer_nr
bill_to_cutomer_nr


Because it is relational, column order is not significant, but when
people look for stuff, it is 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 =3D '<something>' ORDER BY COLUMN_NAME
because all the customer stuff would sort together.

What do you think?

Thanks,

Bill
--CELKO-- replied to bill on 24-Nov-09 08:01 PM
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 replied to --CELKO-- on 28-Nov-09 05:34 AM
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-- replied to bill on 29-Nov-09 12:38 PM
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)


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.