Skip to content

Instantly share code, notes, and snippets.

@ralphschindler
Last active November 2, 2017 15:02
Show Gist options
  • Save ralphschindler/d40b61d4436ae8b6900f11757ef1f872 to your computer and use it in GitHub Desktop.
Save ralphschindler/d40b61d4436ae8b6900f11757ef1f872 to your computer and use it in GitHub Desktop.
Ralph's Database Column Organization

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment