Ralph's Database Column Organization

database-column-ordering.md

Ralph's Database Column Organization

TLDR:

1. primary key columns (e.g. id)
2. foreign key columns (e.g. other_id)
3. row qualifying columns (e.g. status)
4. entity identification columns (e.g. name, title, slug, base_url)
5. non-string-based entity attribute columns (e.g. rating, is_admin)
6. string-based entity attribute columns (e.g. short_description, description, notes)
7. timestamps (e.g. created_at, updated_at)

"Rules"

Each rule applies in order if it pertains to a necessary column in the set of columns.

1. First, Columns used as primary keys. They should be named id unless there is a compelling reason not to (ie: it is not a surrogate key, or it is part of a composite key).

2. Next, all columns used as foreign keys. These keys generally should be named <other_table>_id when there is no other contextual specification. If addition context is necessary, it shoud be prepended to the name. For example, instead of user_id pointing to users.id, if it was a nullable relation and the contextual attribute is "primary", as good choice would be primary_user_id.

3. Next, row qualifying fields. These would be columns that qualify the row and fairly commonly used in search criteria, for example status might be an enum of Active, Inactive, or is_active a boolean/int of true/false. The main concept here is there is a high level peice of information that differentiates sets of rows.

4. Next, columns that help identify rows in human form. Examples here include name, first_name/last_name, title, base_url, slug, etc.

5. Next, non-string based attribute columns. Examples here include star_rating, is_admin, age, birthdate, comment_count etc.

6. Next, string based attribute columns. Examples include description, notes, content, etc.

7. Finally, timestamps. Generally, these are created_at and updated_at

What would the ordering look like for a blog post?

id
author_id
primary_category_id
status
title
comment_count
content
created_at
updated_at

Background reading:

http://stackoverflow.com/questions/894522/is-there-any-reason-to-worry-about-the-column-order-in-a-table This deals with performance implications of column order and years of using these kind of rules have informed common practices.