MyStuff is a project to help minimalists and travelers track their belongings. In earlier posts, I specified how the project was going to work, and what features were going to be included in version 1.0. Then I drew out the wireframes, and built the individual pages.
Now it’s time to setup the database. As specified earlier, the project is going to be built using PHP and MySQL.
Let’s go through and re-iterate all of the features of the project.
- Users can sign up, and log in
- Users will be able to add, edit and delete items from their personal list
- Users will be able to add, edit and delete categories
- Users will be able to change their privacy settings
- Items will consist of a name, description, photo, category, weight, and rating.
The ‘users’ Table
The first concept I’m going to tackle is the users. What kind of information do we need to store on the users?
Well, not much it seems.
- Privacy Setting
- List of items
A pretty straightforward list, but we need to think in terms of relational databases. Each user has a single name, email address, privacy setting and password. These fields represent a one-to-one relationship, so we can store them on the same row as the user.
However, the items of the user’s list represent a many-to-one relationship. In order to represent a many-to-one relationship, we should add a field to the table of the item that is ‘many’ that relates it to the correct row of the table of the item that is ‘one’.
In order to create that relationship from the item to the user, we should give the user a unique ID number. In fact, it’s a good idea to give every table you create a unique ID column, so that you can distinguish between rows based on a value that will not change. For instance, if we used the user’s email address to connect the relevant items to a specific user, we would have to update every single item if the user decided to update their email address.
So now that we’ve got that straightened out, here’s what we’ve got for the fields of our ‘users’ table.
- Email Address
- Privacy Setting
So, now we’ve got our fields for our users database. In PHPMyAdmin, we create a new table, call it ‘users’, and create 5 new columns.
When selecting data types for your new columns, you want to make sure you pick the data type that actually represents your data in the most logical, and efficient way.
You can set the ID field to almost anything you want, as long as each row added to the table gets a unique ID. I suggest using a numerical value of at least size INT(11) and setting it to AUTO_INCREMENT. This way you won’t have to do any manual calculations later to make sure each row gets a unique ID.
The name and email fields should be set to VARCHAR. Some names and email addresses can get lengthy, so it’s best to set these values to 255.
For columns that represent a simple binary switch (on or off), I prefer to use an ENUM of simply ’0′ and ’1′. This makes manipulation of the values a little simpler to manage via PHP, and within PHPMyAdmin. However, a BIT or a SMALLINT would also work.
The ‘items’ Table
The ‘items’ table is also going to be relatively simple. We mentioned in the last section that we’ll need a field to connect each item to it’s appropriate user. Here’s a list of the fields of the ‘items’ table, based on the specifications and what I’ve discussed so far in this article.
- User’s ID
The ID should be at least an INT(11), and the Name a VARCHAR(255).
The Description of an item could get relatively large when compared to the name of the item, so a VARCHAR may not be the best fit. Instead, I suggest a LONGBLOB. That will give your users enough space to enter as much information as they could need.
The Weight will be set to a DECIMAL with the values 6,2.
The Rating will be an ENUM as well, with values “0″, “1″, “2″, “3″, “4″, and “5″.
The Category should be an INT that will represent the ID of a category, which we’ll touch on later.
And lastly, the User’s ID to connect the item to.
The ‘categories’ Table
The ‘categories’ Table will simply stores all of the categories added. This consists of just a name and id.
With the database in place, now we can start developing the PHP classes to cooperate with WordPress and MySQL.