Demian L. Neidetcher| SQL naming convention

I read this article on good SQL naming conventions. It's easy to overlook a SQL naming convention. It's important, here's ours.

Everything is lower case.
An underscore separates words like_this.
Tables are not named with the plural; so user not users.
All primary keys are named 'id'.
All optimistic locking fields are named 'version'.
All foreign keys are [qualifier]_[table]_id. The qualifier is optional.
Use words instead of acronyms as much as you can and where it makes sense.
Never abbreviate anything.
Look hard for common names in tables. Make sure you don't have stuff like create_time and born_on_date.
Spell things correctly, yeah, look it up.
Don't name a table [something]_table (it's been done).

I like naming foreign keys like that because your schema becomes more predictable. You can know the columns in the table and instantly know the intent of the foreign keys. Just like an API, predictability is a big asset.

In my situation I wrote a service layer generator that reads the schema. So any naming or structure in the database instantly effects the API. This is the case at least at the beginning of the project. For that reason, bad decisions when naming database tables effect the entire architecture.

Any time you need a translation layer you are adding drag to the project. By translation layer I don't mean going from protocol X to protocol Y. I mean when you refer to something in the database and then give it a different name in the web-site this slows down your project. Every time a developer crosses that boundary they have to spin cycles translating concepts. Every time a developer that primarily works on the web-site has to talk to the service layer developers there is automatically a disconnect that must be surpassed before more valuable discussions can begin.

As in naming variables, creativity isn't needed. Save the creativity for when you really need it.