DB naming conventions - should I incorporate rules Lowercase, Underscores separate words, Full words, not abbreviations.

I have a large application that I am rebuilding as an Aurora/MySQL database. This has provided an an opportunity to start over with the naming conventions in my MS SQL database which has become a mess over the years. I am looking at incorporating some new rules and I am looking for additional pro/cons and rules I should follow.

1. Lowercase
2. Underscores separate words
3. Full words, not abbreviations
4. Prefix objects with application module name - I currently prefix objects with application module name that the object belong to. Is this a bad idea?
justinfilmerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
I usually use 2, all are fine except for 3, as spaces in names force the use of square brackets [ ] around them.

Just pick one (1,2 4) and stick with it.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Those name conventions are ok.
You just need to add no usage of special characteres (space and accents).
You should also have a length limit. I would set if to 30 chars, since is the Oracle ans Sybase limits (MySQL is 64 and SQL Server's 128) so if you want to migrate in the future you won't have problems with object names.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Data DudeCommented:
>I currently prefix objects with application module name that the object belong to. Is this a bad idea?
Another option you have is to use schemas for 'module name', such as sales.sales, customer.customer_address, and inventory.PartsList.  Takes a little more work as now you have to prefix everything with the schema name, but this also enables you to apply security on schemas and not individual objects.  Your call.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
1. Your choice. Some people prefer to use some upper case.

2. I strongly prefer the use of underscores, camel_case, vs. using "camelCase".

3. Within reason. You might also want consider designating specific abbreviations to be consistently used.

4. Yes, I think that's a terrible idea to insist on. Objects can be shared across many apps (or they should be). What "app" does a "customer" table "belong to"?
justinfilmerAuthor Commented:
I appreciate the additional info regarding object name length.
John KawakamiCommented:
Prefixing with the app name was a way to get around the fact that web hosting companies gave you only one database.  If you aren't limited that way, just create a new schema for your application.  You can join across schemas.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.