7 rules for designing your first database schema or data model

#database #design #sql #webdev

Original post: 7 rules for designing your first database schema or data model
Do you have any tips or rules of thumb for designing your database model? Love to hear pros/cons in the comments!
Are you a novice or intermediate web developer, starting a brand new project that uses a database? Not sure where to begin in terms of your table design (schema) or ORM models? I've advised quite a few students on this exact topic before, so I assembled these tips as a "check list" to use when you are designing your database:

Think about "nouns" - your models (tables) should be the main "nouns" of data that you have to keep track of (e.g. Book, Author, BlogPost, etc). The methods (functions) should be the "verbs" (e.g. get_latest or update_messages) and the properties (columns) should be adjectives or relationships (e.g. popularity or color for adjectives, or creator for a relationship)
Combine similar models - If two models (tables) seem almost the same, with maybe just one or two fields (columns) that are used only on one but not the other, just make them the same, and just skip using the fields (columns) that are specific to one when adding data for the other. For example, when developing the LiveSyllabus LMS, I was originally going to have separate models for Homework Assignments and for Reading Assignments.... but pretty quickly I realized they are almost identical, so it makes sense to store them in the same model (table) and have a category field to distinguish them. Another example: Don't have books_romance and books_horror as your tables, instead just have a books table and have genre be a column.
Simple and stable - No one likes lots of database migrations. So, try to design your models to last and not have to be changed often.
Think visually - If it helps, think about how your application might function if it were in a spreadsheet. What columns do you need? Which values could be computed from other values?
Compute what you can - Avoid storing things that change or are computed in your models. For example, even if the design requirements need to show "how many days until we want this to be published", what we really want to store is the unchanging "publish date". The publish date doesn't ever change, it simply is used to show or hide the article depending on the current time.
Match what you present - Think in terms of "data" vs "presentation". Often, what's stored in your models might be pretty close to what's presented, a 1:1 relationship. Other times, the database will be simpler than what's presented (frontend), and it won't match. In this case, think in terms of putting on a "show" for the user.
Don't over-engineer it - Start small. Don't think of every possibility, just cover the top ones. In some cases you might need a n:n or "Many to Many" relationship (e.g. for a Twitter clone: "friendships", "followers", "likes"). But in other cases, resist the urge to model all possible features at once. As an example, maybe you could think of a future feature where a Tweet might have many User author -- but unless that feature is a "must have" for your product now, start with each Tweet having only one User (One to Many or 1:n) until you finish other aspects of your software. Keep your product's design short and sweet. Most software products won't ever even get used, let alone improved upon.

Bonus (Django specific) rule: Avoid "Generic Foreign Keys" if you can!

Background: A student recently asked me what is the best practice for designing data models. While the question was originally for the Django ORM on PostgreSQL, I've given the exact same advice while teaching classes on JavaScript / Node / Express stack using SQLize and MySQL, and in another course, Mongoose for MongoDB. While I've never taught a class on Ruby on Rails, the same advice also goes for ActiveRecord. These tips are pretty universal! In fact, as a Star Trek fan, I'll point out that even Star Fleet needs good database design: