As we discussed last week, planning your database structure ahead of time has many benefits. Today we’ll be going over (in a general sense) how to create a database plan.
Let’s start out with tables. A table contains a structure made up of fields. Conforming to the structure are rows of data. Think of a table like a spreadsheet, where fields are the “headers” at the top of a column, and data is separated into rows.
The best way of deciding what tables you want to create is relatively simple. Within your database, you’ll probably need to store a few different types of data. If we take the example of a blogging platform, you’ll need to store (at minimum) posts, comments and options. These are three different types of data. As these data types will require different fields, we will create a table for each data type. Generally, this is a good way of determining what tables you will need to create.
In this example you could possibly store posts and comments within the same table (as they have similar field requires). However, I would recommend against this. Because there is a potential to have thousands of comments and thousands of posts, combining these two data sets will only slow things down. This is another thing to consider when planning out your tables. If you have two types of data that could conform into one table, will one (or both) of these data types potentially end up with thousands of rows. If so, you should separate them.
There is a caveat on that last point. If those two similar data types are directly related to each other, and you will always need the other within your application, you may want to keep them within the same data row. This is because performing a query to retrieve this data involves less load on the server if they’re in the same row, than if they were in separate queries. For instance, you’ll always need the comment author and the comment body together, so it makes sense to keep them within the same row, and not in separate tables (not a good example, since they both fall under the “comment type”, but you should get the point).
Onto fields, which are much simpler to plan out. Basically, you’ll need a field for each type of data within a general type of data. For the example of a post table, you would need a field for when the post was created, the title of the post, the body of the post and any other features the blogging platform might have (categories, tags, etc).
The expandability of a database structure comes from making your tables general enough to support additional fields within in the future. Keeping your data types general, while not making them too general, is the basis of a good database structure. While still trying to make your data types general, you should follow the above guidelines that I have set out in this post and not make them too general.
Because I like you guys (although really because I feel like I’m writing to myself… and I like myself), I’ve made an visual (incomplete) database structure for the example we used throughout this post. Obviously you don’t need to make a plan this “formal” (a few notes jotted on a piece of paper, in a text editor or, if you have a good memory, in your head will work just fine.
