Online Orders Database

The online orders database is a custom web-accessible database for storing the customer and order information to aid production and sales of Chap Remotes. Created over the course of a summer as a way of teaching database and web programming, it has since processed over 150 orders. It uses a mySQL database with a WordPress interface in order to provide an internal system for processing orders. Each user has a login and role (courtesy of WordPress), so every member of the manufacturing team can do their part in marking items as shipped, replying to customers, requesting payment (via PayPal integration) etc. While of course the system still has much area for improvement (such as automated emails or customer log-ins), we finally had to lay it to rest in order to move on to other projects. However, it has served as a valuable learning experience, as we move forward to utilize these web development skills!

Final Website Collage Project Leader: Rachel Gardner
Contributors:

Date Completed: October 2014
Link to Code: GitHub Respository
Tags:

  • Software
    • Web Programming
      • HTML
      • CSS
      • Javascript
      • PHP
    • Database Programming
      • mySQL

Planning

Whiteboard Collage Like anything else, the design for the database began on the whiteboard. We laid out the functionality we would need, then moved on to determining the mySQl tables that would service those needs. We began drawing up what kind of forms we would want, discussing details such as whether we wanted to keep phone numbers and how we would store information about the kinds of products available. In the end we even decided we’d give up on making an inventory management system for now, given the scope of simply tracking orders. After several hours (and a whole lot of whiteboard drawings), we ended up with a complicated system of 6 tables: customers, orders, items, packages and pieces.

Creating the Database

We began coding in mySQL in order to create the “schema” for the database. The various tables are detailed below. Don’t worry, we had just as much trouble wrapping our heads around them as you will.

  • Customers – The individuals or organizations ordering ChapRs (given by unique customer IDs). Note that a return customer is counted as another customer (due to time constraints)
  • Orders – The full “order” for a customer, including status of payment and date ordered. Orders are linked to customers and packages through various IDs (therein lies the true magic of SQL).

  • Items – The last “dynamic” table, each entry in this table represents an item in an order. This includes the ID of the item and the quantity.
  • Packages – The available groupings of pieces. For example, the FRC ChapR is a package (it includes a FirePlug, 9V battery, ChapR etc.) Each entry in this table contains data on the price and status of the package (whether or not it’s active). This table is static for the most part (it is only updated as we release new products).
  • Pieces – The various components of packages (such as batteries, USB cables etc.)
  • PvP – The table (Packages vs Pieces) that details what pieces go into what packages.
Create DB CloseUp

Integrating with the Web

Web Code Collage Using a custom WordPress template, we coded the display in PHP. To do this, we first began by making each of the forms in HTML. From there we devised our own PHP functions to generate the HTML, then created PHP files to validate the data and pass it into the database. Next it was just a matter of taking data from the database and displaying it in pretty tables using various custom templates. Finally we made those tables interactive: clicking on a row would bring up a page to edit the order/customer (including requesting payment via PayPal). Several rounds of bug testing later and we had a working web interface!

Reflections

As one of the most condensed projects of Chap Research, coding the online orders database was an incredible learning experience. Rachel (the only Chap Research student on the development team) started from not even knowing HTML to owning and contributing to every aspect of the incredibly industry-applicable project. This paved the way for her seminar on web development. The application itself has proved tremendously useful, allowing us to process hundreds of ChapR orders.