- 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.