Best practices for ordering columns in SQL when creating the table

Question

I have a table foo which contains the following columns

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

Are there any best practices in following a ordering convention for all the columns (e.g. in a alphabetical fashion or not nulls at the top and rest of the columns at the bottom)

Answer 1

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!

Answer 2

Not only is there no real convention, but it's best not to think about one. Sometimes insert queries, for example, will not specify field names when it's assumed that you're going to update every field. But then you have to specify values in an assumed order. This is error-prone. So the best way to think about it is, "these columns are present in this table, in no particular order."