what is the standard casing with database tables and fields

Question

is there a standard or best practice with database table names and fields in terms of casing (upper, lower, camelcase, etc. . ) ?

Answer 1

There is not standard defined by a database (or if there were, it would depend on the database) but many frameworks suggest standards.

For example, MS Access often uses a prefix like tblPeople notation, whereas Ruby on Rails would call the table people and use_underscores_between_field_words.

It really depends on the environment and culture that most often uses that environment.

Answer 2

It depends on the platform, but the convention that ActiveRecord (Ruby on Rails) uses is lowercase with underscores:

first_name
last_name
home_street_address
person_id

Here's a good article on ActiveRecord Conventions.

Answer 3

There doesn't seem to be any universal standard. My personal one is that tables and columns are named with Pascal casing, table names are always singular (e.g. Customer, SalesOrder, Product, etc.)

Column names are also Pascal-cased - and I use a prefix for primary and foreign key fields (pk and fk respectively) but I know some frown upon this.

Answer 4

I think that keywords (e.g. CREATE, TABLE, and SELECT) might be conventionally upper case: so, not upper case for user-defined identifiers.

Answer 5

You will get as many naming convention standards as you get posts to this question. Probably more. If there was a best standard or best practices, we'd all have heard about it by now.

Fortunately, there are two known worst naming conventions: no naming convention, and it's evil twin inconsistant naming conventions. Whatever you decide works, use it and stick with it; but if you are modifying existing code, don't try to worm incompatible conventions into any existing standards!

Answer 6

If you're entering a situation where programs already exist or you are working with other programmers, ask someone that's been there longer than you what they use. There really isn't a standard that is universal. One contract I was on wanted underscores between words. Another contract is all camel casing. Some (mostly in Access) preface the the objects (table, query, etc.) with tbl, qry, and so on. That's mainly a carryover from old-school VB6 because VBA is so closely related.

However, as many have said, there's no cut and dry answer, so if you're alone on a project, do what works for you. If you're on a team or coming into an existing project, ask someone that's been there longer. The only universal rule is to give your tables and fields meaningful names. No one coming in behind you will want to deal with tblX14ForPaul and fields X, sline, and clrMd.

~Sark