Database schemas

From foldr
Jump to: navigation, search
  • Name things after what they are, not after the tables they reference. For example, author_id is better than user_id, and authorships is better than posts_users.
  • Surrogate keys are better avoided unless necessary. Writing queries is much easier with natural keys, as it requires fewer joins. Browsing tables manually is easier for the same reason.
  • When having a surrogate key, prefer uuid over serial. If you ever had the misfortune of having to merge two databases, like I did, you'll know why.
  • You can't design a good database schema if you aren't using PostgreSQL.