Yesterday we talked about the first step in writing a plugin, to have a solid plan written out before ever starting to write code. The next step in the process of effectively writing a plugin is to thoroughly map out and create the database we will be using to host the data we collect with this plugin. WordPress natively uses MySQL, the most dominate open-source database and integrates extremely well with PHP. While we will be discussing the strategy and process for establishing this MySQL database, we will not be getting into the step-by-step process of making the database or how to manually insert data into it. There is already an exhaustive list out there on how to do so, both online and in printed form.
Defining & Creating the MySQL Database
Completion Time: 1 hour
What We Will Be Using: Pad And Paper, MySQL database (myPHPadmin)
Decide What Data To Collect
Before even getting into the MySQL database, we need to layout what information we will be collecting and how it should be stored. The best advice to be given here is to dream for the stars. Once the first version of the program is completed and sent out to be distributed, it will be an extreme pain to delete parts of the database and impossible to add new rows. If you think that there is even a remote chance that you will be using something, add it to the database now to be tested.
As an example, we realized that we would be collecting the title of the game, a description of how to play it, materials we needed, the date it was submitted, if the game is pending approval or ready to be displayed, and the always present unique game ID to track it in the database. We put this list down for a moment and began to see if we needed more tables or items to cover everything we wanted to do. After looking at our plan for an individual game, we realized we wanted to track the person who submits the game as well as a comment and rating system for each game.
Effectively Map Database Tables
After coming up with with a rough draft of what you want, it is time to begin testing. This is the perfect time to theoretically test the structures you have come up with on paper or a whiteboard. Display what information would be put into the database and then how you would like to see it displayed. This may have already been done in your planning if you were thorough enough. Look for any inconsistencies in your logic.
One thing that we noted when we went through this process is that we could not simply relate the comment ID to the game. One database style is to create the game and game type tables and then relate them to each other through a meta database. This meta will reference the a game time to a specific game, but still allow us to reference that same game to a different game type. This provides greater flexibility and eliminates any redundancy in data. (See the solution we came up with in the diagram at the end of the post)
Optimize Your Database
This is the time to go back through your draft of the database and see if you can optimize the different parts of your database. Should the title of each game be 256 characters long? Shortening it to 128 characters would speed up accessing data when someone loads a page on your website, but limit what people could call their games.
At this point, we need to make a judgement call, which is more important: download speeds or flexibility for the user? This is something that should be tracked and researched, even after the program has been written and distributed to others. Some of the best companies, Google and Microsoft, never get it right the first time, but consistent and effective tracking, analyzing, and improving their products have allowed them to create superior tools.
The final beta database we came up with is shown in the diagram below: