Welcome to my notebook

Just a few notes here to help me remember what I’ve decided on for naming object in MySQL. It should work for other databases as well, but there might be some additions.

Objects

No Quotes Quoted identifiers are hard to work with. I think I’ll just avoid them. That means no white space either.

Ex: Avoid names like "LastName" or "Large Boxes".

All Lowercase use all lower case letters for all object names. This aids portability if you have to move to another database or operating system.

Ex: Use names like last_name, not LastName.

Underscores separate words Object names that are composed of multiple words should be separated by underscores (otherwise known as snake case)

Ex: use page_length, not pageLength.

No abbreviations Where possible, abbreviations are to be avoided unless extremely common within the domain.

Ex: last_name, not lst_nm. Although dhs_building would be acceptable if you were working for the Department of Homeland Security.

Short Try to keep object names to 30 characters or less. Long object names can make your code really hard to scan and format.

No reserved words Duh.

Singular Table Names

Using table names like person instead of people avoids ambiguity and aids consistency. They also map better to ORM systems where you tend to work with collections of singular items.

Key Fields

Primary Keys

Surrogate and auto generated just call them id and be done with it. ORMs love it and you don’t have to think too hard about the name while coding. Even if you decide that a primary key should be some sort of user designated (short) string, I’d just stick with id instead of something like code. Sometimes people want to prefix the name with the table name, but this is redundant and not necessary. You’re already referring to the table somewhere in order to reference its primary key.

Complex keys should just follow the normal column naming conventions. An alternate approach to using a complex key might in some cases be to make the complex key a unique constraint on the table and add a surrogate id key.

To name the primary key constraint, pk_tablename is often generated by the database already, but you can specify it directly if your db does some craziness like pk__tablena_01389493902. In general, it’s usually better to explicitly name constraints. If you build the db from script on a different server, you want to be sure you have the same name.

Foreign Keys

Here you’re going to use a combination of the referenced table and _id for the field name in the referencing table.

Prefixes and Suffixes

We’re just not going to use these on tables, views, functions and procedures. We can tell procedures and functions from other objects by the use of verbs in their names.

Schemas

MySQL doesn’t support the idea of segregating objects by schema, but a quick trick is to use a schema as a prefix to the table name. Normally, this would not be my approach, but sometimes you have strong logical separation of objects that can be helped with this approach. If you have a set of tables used to support an authorization system, it would be fine to prefix them with something like auth_. You might then end up with some table names like auth_user, auth_group, auth_policy, etc. and they would all sort together in your db tools.

Explicit Naming

Indexes

Generally use the form of tablename_ix_column1_column2 to show the table, index type and what is indexed. For a check constraint, you can use tablename_ck_email_lower_case to describe the constraint as much as possible just in the title. Again, it makes it easier to tell what is going on in your db tools without having to dig into a bunch of screens to expose the details. It also helps to make the explain process easier to understand.

The same goes for check constraints if you can tell what went wrong from the error message.

Foreign Key Constraint

Use the table_name_foreign_column_id_fk. Again, this makes the error message you may get descriptive enough for you to track down the problem without having to dig into the db tool interface too far.