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.
BlogPost, etc). The methods (functions) should be the "verbs" (e.g.
update_messages) and the properties (columns) should be adjectives or relationships (e.g.
colorfor adjectives, or
creatorfor 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
categoryfield to distinguish them. Another example: Don't have
books_horroras your tables, instead just have a
bookstable and have
genrebe 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
Tweetmight have many
Userauthor -- but unless that feature is a "must have" for your product now, start with each
Tweethaving 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!