19

Possible Duplicate:
Database, Table and Column Naming Conventions?

Each time when new project starts, I’m thinking about naming conventions of table and columns in database. Which case is your recommendation and why?

Case 1. column_name
Case 2. ColumnName
Case 3. Column_Name
Case 4. columnName

Community
  • 1
  • 1
sasa
  • 2,443
  • 5
  • 23
  • 35

8 Answers8

28

You should use case #1 because it's free of case sensitivity problems. Also, camel case sucks with acronyms.

columnID
columnId
columnIDAlternative
columnIdAlternative
RASCScore
RascScore

column_id
column_id_alternative
rasc_score

Also, spaces between words are visually more pleasant than jamming everything together. Absolutely worth whatever perceived pain of typing an underscore there is. Underscores simulate spaces and compound nouns and phrases have spaces in normal, written language. TheOnlyPeopleToTypeLikeThisMayHaveBeenTheRomans.

Mark Canlas
  • 9,385
  • 5
  • 41
  • 63
  • 3
    I have never liked CamelCase and you just explained why. – Xeoncross May 05 '10 at 19:59
  • 2
    When you start using Underscore initially it looks good, later after a year or so you will feel irritated. Especially when you have ORM framework used in .Net – Ravi Vanapalli Nov 25 '13 at 12:28
  • Could you elaborate on what the link is between table names and an ORM framework in .NET? – Mark Canlas Nov 25 '13 at 12:36
  • What's about data mapping? It's painful in the json era if object has property with underscore , `user.postDate` is obviously better than `user.post_date` – TomSawyer Aug 15 '17 at 16:22
  • ORM = Object Relation Mapping, it's ok to use underscore in 2009, nowadays, using underscore in object properties considers as bad naming convention. – TomSawyer Aug 15 '17 at 17:36
  • 1
    @TomSawyer I would rather let my ORM to convert the case to fit the language. For example, in JS I want `lowerCamelCase`, while in C# I want `PascalCase`. Should I change the database when I shift language? – Franklin Yu Oct 22 '18 at 14:25
7

Whatever you decide to choose, sticking to the same is most important, that is be consistent.

I prefer #2 as this is imo most readable and as mentioned before underscore is ugly and annoying to type. #4 is second best. #3 i like the least, both uppercase and underscore is overkill.

Joakim Elofsson
  • 36,326
  • 1
  • 22
  • 28
6

I agree with #2 for two reasons:

  1. Underscores ARE a pain to type.
  2. In .Net properties are usually cased this way. This makes all your naming match up - which is handy and helps in situations where you are using an ORM.

Coincidentally, I believe Java developers trend to use #4 in their classes. I'd change my answer to #4 if the client software is in Java.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
nikmd23
  • 9,095
  • 4
  • 42
  • 57
3

I use case 2 (ColumnName) - because underscores are a pain to type.

Underscores are OK in index names, triggers, or other objects that aren't frequently typed. I leave them out of tables, columns, views, stored proc names since those are names that are frequently used and reaching for that underscore can slow you down if you use it often.

Scott Ivey
  • 40,768
  • 21
  • 80
  • 118
3

I vote for "Whichever one you used in the previous project" Consistency in this case is probably more important than any particular ideology...

Brian Postow
  • 11,709
  • 17
  • 81
  • 125
2

I Like case 2, values seem to stand out to me better that way. Whatever you pick, keep it consistent!

KM.
  • 101,727
  • 34
  • 178
  • 212
1

I name my tables exactly the same as I name the objects which I am going to create to wrap them. This works well with modern ORMs (Object relational mappers) because they can often create an object model for you based on your database structure (or the other way around). The accepted answer seems to belittle the "pain of typing an underscore", but I take it seriously. Suffering from RSI, especially in my pinkys, which I use to hold down the Shift and Ctrl keys, I do absolutely everything I can to avoid unnecessary underscores. Of course, a good answer to this issue is to remap your CapsLock key to either the Shift key or the Underscore key. But in any case, I add this answer to an old question because no one mentioned working with your ORM. Since I do most programming in .NET most of my properties are camel cased and so I name my db columns in camel case as well. I have absolutely no problem camel casing abbreviations. So I do things like:

PersonDao.GetIdByName("Hello world").

Camel casing definitely gets annoying for long names... but then, I avoid long names. Usually it means that I've organized things incorrectly. And if I determine that I haven't, well... in those cases, long names are so rare in my code and the situation so unique, that it doesn't slow me down anyway.

I think naming's absolutely of paramount importance. And just like some people have an XML fetish, others have database fetishes. Personally, I like to use my ORMs to ignore my database completely (or as much as possible). To facilitate this, I name my columns just as I name the properties in code. So, ultimately, underscore hating aside, I use whatever conventions exist for the language my code exists in.

argyle
  • 1,319
  • 2
  • 14
  • 28
  • Sure, that's ok if using the one programming language. What if multiple languages are used to access the DB? At the end of the day, surely readability is orders of magnitude more important that having to press the shift key. – BrendanSimon Sep 08 '20 at 03:46
1

I just love camelCasing (4) great readability, no underscore

tekBlues
  • 5,745
  • 1
  • 29
  • 32