Ralph's Database Column Organization
- primary key columns (e.g.
- foreign key columns (e.g.
- row qualifying columns (e.g.
- entity identification columns (e.g.
- non-string-based entity attribute columns (e.g.
- string-based entity attribute columns (e.g.
- timestamps (e.g.
Each rule applies in order if it pertains to a necessary column in the set of columns.
First, Columns used as primary keys. They should be named
idunless there is a compelling reason not to (ie: it is not a surrogate key, or it is part of a composite key).
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_idpointing to users.id, if it was a nullable relation and the contextual attribute is "primary", as good choice would be
Next, row qualifying fields. These would be columns that qualify the row and fairly commonly used in search criteria, for example
statusmight be an enum of
Active, Inactive, or
is_activea boolean/int of
true/false. The main concept here is there is a high level peice of information that differentiates sets of rows.
Next, columns that help identify rows in human form. Examples here include
Next, non-string based attribute columns. Examples here include
Next, string based attribute columns. Examples include
Finally, timestamps. Generally, these are
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
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.