Best Practice for Naming SQL Table Columns

Database, Table and Column Naming Conventions?

I recommend checking out Microsoft's SQL Server sample databases:
https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

The AdventureWorks sample uses a very clear and consistent naming convention that uses schema names for the organization of database objects.

1. Singular names for tables
2. Singular names for columns
3. Schema name for tables prefix (E.g.: SchemeName.TableName)
4. Pascal casing (a.k.a. upper camel case)

Sql naming best practice

prefixes like that are pointless, unless you have something a little more arbitrary; like two addresses. Then you might use address_1, address_2, address_home, etc

Same with phone numbers.

But for something as static as age, gender, username, etc; I would just leave them like that.

Just to show you
If you WERE to prefix all of those fields, your queries might look like this

SELECT users.user_id FROM users WHERE users.user_name = "Jim"

When it could easily be

SELECT id FROM users WHERE username = "Jim"

How should I name my columns in my SQL database?

I prefer lower case always, like

to_settings

Check this blog
https://www.xaprb.com/blog/2008/10/26/the-power-of-a-good-sql-naming-convention/

Table field naming convention and SQL statements

For queries that aren't ad-hoc, you should always prefix every field with either the table name or table alias, even if the field name isn't ambiguous. This prevents the query from breaking later if someone adds a new column to one of the tables that introduces ambiguity.

So that would make "id" and "name" unambiguous. But I still recommend naming the primary key with something more specific than "id". In your example, I would use student_id and teacher_id. This helps prevent mistakes in joins. You will need more specific names anyway when you run into tables with more than one unique key, or multi-part keys.

It's worth thinking these things through, but in the end consistency may be the more important factor. I can deal with tables built around id instead of student_id, but I'm currently working with an inconsistent schema that uses all of the following: id, sid, systemid and specific names like taskid. That's the worst of both worlds.

Best practices for ordering columns in SQL when creating the table

Certainly the primary key first.

The name of that column is usually tablename_id (traditional) or just 'id' (preferred by frameworks such as rails).

If used, usually a name/description field next, as you have it.

I tend to put foreign keys after that (parents first, children after that) as they tend to be more critical during development.

Then I group other data, e.g. address line, city, state, zip together.

When no other rule fits, I tend to prefer required fields higher up for increased visibility.

timestamps (some/all of created_on, updated_on, removed_on, etc.) usually last

So in your example I would actually do:

create table foo (
id integer not null auto_increment unique,
name varchar(255),
type tinyint(1),
desc varchar(255),
modified_time datetime not null
)

Note - As Kolink noted, use description over desc because desc is a reserved word meaning descending, e.g. order desc

However... big disclaimer...

if your table changes over time (i.e. real world) and you have existing production data, you will not have the fields ordered as initially 'planned'. This can be avoided by export and re-import but in many cases its best to accept that ordering is just a convention for initial creation for programmer convenience and that alone.

Another popular topic here is column name naming conventions. That's a whole 'nother topic but I'll tip my toe in by saying don't abbreviate unless forced!

MYSQL table and column naming convention

Long table and column names do not have (any significant) performance impact. All tables and column references are turned into internal locators during the compilation phase of the query. So pretty much the only impact is having to query a longer query string. The parsing part of query compilation is usually ignored from a performance perspective.

The following is opinion-based. As a general rule, I follow these conventions for naming:

• Table names are in the plural, because they contain multiple entities.
• Each table (almost always) has an auto-incremented numeric primary key, which is the singular form of the table followed by Id.
• This column is the first column defined, so I can use order by 1 desc to get the most recent rows added to the table.
• The table name is not (generally) part of the column name. I always (try to) use table aliases, so including the table name would be redundant.
• Foreign key references use the same column name as the primary key they are referring to, when possible, so I can use using for joins.

I admit that these are "opinion-based", so the real answer to your question is in the first paragraph.

What is the best practice for naming database tables to provide natural organization?

I tend to go against the grain in naming conventions on two counts here...

1. I don't like using prefixes so that things group together in a given UI. To me the tables should be named such that in code they are easily readable and make sense. There have been numerous studies (mostly ignored by programmers) that show that things like using underscores, logical names, eliminating abbreviations, and eliminating things like prefixes have a very big impact on both comprehension and the speed of working with code. Also, if you use prefixes to break down tables what do you do when a table is used by multiple areas - or even worse it starts out in only one area but later starts to be used in another area. Do you now have to rename the table?

2. I almost completely ignore name lengths. I'm much more concerned about readability and understandability than I am about taking 1/10th of a second longer to type a column or table name. When you also keep in mind all of the time wasted trying to remember if you abbreviated "number" as "no", "num" or "nbr" and keep in mind the use of things like Intellisense, using longer, more meaningful names is a no brainer to me.

With those things in mind, if your UserEvents table really has to do with events related to a user then that name makes perfect sense. Using just Events would end up giving you a poorly named table because the name isn't clear enough in my opinion.

Hope this helps!

---

Related Topics