The Better Developer

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.

• Name
• Email
• Password
• 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.

• ID
• Name
• Email Address
• Password
• 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.

• ID
• Name
• Description
• Weight
• Rating
• Category
• 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.

In my previous article I specified a new project called MyStuff, a web application built with WordPress, PHP and MySQL to help minimalists and travelers track their belongings. I developed a flowchart for the application that describes how it works.

MyStuff Version 1.0 Application Design

Now that the basic layout of the website has been spelled out, I’ll need to find a design to use for the site and design the individual pages. I’ve decided to use the free Responsive design at WordPress.org as a starting point for MyStuff. The design is clean enough that I can make modifications to it fairly easily and its built to scale down on tablets and mobile phones, which is perfect.

The responsive design featured at WordPress.org scales to fit mobile devices in an intelligent way.

Wireframes

Web developers and Application designers often build out wireframes for their projects. This allows them to try a few different layouts before getting lost in the code for the project.

Here, I’ll explain the wireframes I designed for MyStuff. These are all based on the Responsive design I chose earlier.

Home Page

The work for the homepage was practically done for me, but the wireframe was still necessary for me to map out where everything needed to go that needed a place.

The home page of MyStuff has quite a few functions. It’s primary job is to let new users know what the site is for, and to show them how hey can use it. The home page acts as the sign up and log in forms.

Profile Page

I decided to split up the items into enough columns so that one would fit comfortably on a mobile phone.

The profile page is what the world sees when viewing your MyStuff list. It shows all of the information related to each item in the user’s list.

Edit Profile Page

A simple but necessary modification to the profile page.

The edit profile page is very similar to the profile page itself, but includes administration menus.

Add / Edit Item

All of the forms follow this basic look & feel.

This page is pretty basic, includes a simple form to modify or add a specific item.

With these wireframes done, we can get started building out the actual pages in WordPress.

Building out the Pages

The responsive theme I chose came with several base page templates that we’ll be modifying to get our pages built.

Home Page specific problems

The home page template of the Responsive theme is a little unintuitive. It doesn’t reside as a typical wordpress page. Instead the developer chose to build a GUI that you can use to modify its different attributes. Since I planned to do a little bit more to it than the original developer intended, I cracked open the home page php file residing in the theme’s folder.

With a little bit of tweaking, I’ve added the sign up form, and log in forms to the home page. Note that these aren’t functional as of writing this. The idea is to build out the design, and make sure everything has a place.

The Profile-based pages

None of our wireframes used a sidebar, so we’ll be basing almost all of our pages off the full-width template of the responsive design. Now, MyStuff isn’t actually a blog. We’re using WordPress here primarily as a content management system. So we’ll need to create a new template based on the full width template provided to give us a clean slate for developing our pages.

I had to use a bit of PHP to generate the results (no point in drawing out the same box six times just to later redo it in PHP).

In the wireframe, I drew the items 3 across, but 4 items seems to fit better, and translate better onto mobile devices.

The Remaining Pages

The remaining pages are all pretty straightforward. I ran off the completely empty template, and just built out a simple form for each page.

Now that the page designs are completed, I can get started designing the database and PHP classes that will manipulate the project.

My favorite part. ;]

Sound off in the comments below.