- Project Outline -
Project: Design and build a database inventory system for LLoyd Gourmet Cheese Company.
Team: 4 people.
My role and responsibilities: Coding using MySQL, PHP, HTML, JavaScript. Design of the website. User testing. Report writing and editing.
Tools used: MySQL, PHP, HTML, JavaScript, Microsoft Word, Miro.
Date: September 2019 - March 2020.
- Introduction -
The purpose of this project was to design and build a database inventory system for the Lloyd Gourmet Cheese Company (LGCC). The company was looking at new ways to manage their inventory and considered purchasing enterprise resource planning (ERP) software in the near future. Before purchasing the software, they wanted to build a prototype using MySQL, PHP, HTML and JavaScript. The purpose of the prototype was to see how an ERP system could help their business and what advantages it would provide. The process of building the system was divided into three distinct tasks:
- Creating the database design developing forms
- Queries and reports
- Refining the database
The distribution manager for the company was the primary point of contact for our team and he provided the following initial functional specification to help develop the first iteration of the design:
- Design the tables for this database
- Define the fields (data structures) for each table
- Create the tables in MySQL
- Populate the tables with a reasonable amount of data
- Create suitable forms for each of the data tables. Keep in mind the functionality needed for each form. Use validation rules and access form controls were necessary. Make the system easy to use
- Produce any queries or reports dictated by the client
- Produce relevant user and technical documentation
While LGCC initially provided a functional specification, throughout the development of the system our team was in constant contact with them. This resulted in the their needs being integral to each iteration of the prototype. It also reduced the probability of building a prototype which is completely useless for LGCC. This iterative approach was heavily based on the principles of user-centred design which is deployed in designing physical products.
With this methodology in mind the team set about building the system by developing the database design.
- Database Design -
The first step involved in building the system for LGCC was to create a high level database design. This involved creating the necessary tables and data types within those tables based on the customer needs. Following principles of good programming practice the main focus was on the outputs outlined in the functional specification. Once the outputs were well understood the focus then shifted to table’s logic and structure.
Initially there was confusion with regards to what the client was looking for and how the client currently operated their business. This was particularly the case with regards to information about expiration dates and how many cheeses and supplier were involved with the business. These issues resulted in inadequate designs however, this issue were resolved through conversation with the client and gaining more insight into the business. After a couple of iterations a final database design was constructed.
The information was displayed to the customer using an application design worksheet as well as ER diagrams.
Entity relationship (ER) diagram for database design, created using Miro
The appropriate fields were highlighted and color coded with some tables requiring a compound primary key to uniquely identify individual records in a table.
One of the main challenges with developing the database design was understanding the M: N (many-to-many) relationships, which were necessary for this process. Initially the M: N relationship was solved by adding foreign keys to the “many” side of the relationship that points to the primary key of the related table. However, the research carried out on M : N relationships during the database design stage, indicated that this approach was incorrect for two reasons:
- This approach creates a significant amount of data redundancy
- Given the structure and contents of the table the relational operations become increasingly complex and inefficient.
Fortunately, the difficulties that arise with M: N relationships can be avoided by creating a composite entity. An example of composite entities from the database design is the supplier order details table. This tables essentially converts what would have been an 1:M table and creates a 1:M relationship.
Data redundancy leads to data anomalies which can destroy the effectiveness of the database. However, the composite tables in this context are critical in minimizing data redundancy.
Once the customer was satisfied with the database design, the next step in developing the system was to develop the necessary tables, forms queries and reports using MySQL, PHP, HTML, and Java Script.
- Developing the System -
My team and I did some research into how database inventory systems are typically set up. As a result of this research and our increasing familiarity with the problem space, it became clear that the system was essentially split into two aspects. The customer facing side and staff facing side of the application. These two aspects revolved around the inventory and product tables respectively. The customer would use the application to purchase cheese which would diminish the inventory. While the staff would use to application to replenish stock and look at insightful company trends and reports.
Therefore the best approach of explaining the database is through the separate lenses of the customer and staff members.
- User Interface -
- About the User Interface -
A critical component of this system is the user interface (UI) as the system will be used on a daily basis by staff and customers alike. Failure to recognise the importance of user experience would be detrimental to the uptake of the application, therefore the interface should be easy to use and intuitive.
A number of different interface design practices where deployed in the development of the interface and they stemmed from researching the users, their goals, skills, preferences and tendencies.
The first objective was to keep the interface simple and ensure the interface is almost invisible to the user. This was achieved by avoiding unnecessary elements and using clear and concise language on labels and messages.
Another crucial aspect to the user interface was to create a consistent design using common UI elements. By ensuring consistency throughout the application users should feel comfortable and be able to quickly complete tasks. Repeated patterns in the design and layout help to facilitate efficiency. Once the user of the application learns how to complete one task, they should be able to transfer that skill to operate the rest of the application.
Page layout and colour also played an important role in the UI. These features were used to draw attention to certain items and helped to increase readability and scan-ability.
System feedback was also an important consideration in designing the application. By informing users of actions, errors and changes in state it helps to alleviate any frustrations the user may have with the system.
- Design Iterations -
To help improve and refine the UI, the system underwent four separate user tests. It was extremely beneficial to get customer feedback with regards to the usability of the sight and the progress is outlined in the images below.
Comparing the first design iteration to the final design iteration, here are some of the changes that were made based on user feedback:
- Keep a consistent design throughout the website
- Keep the visuals simple. Less distracting
- Include all the cheeses on the order page. That way multiple orders can be made in one go
- Highlight the cheeses that are low in stock and should be ordered soon
- Have a running total cost as items are being added to the cart
- Keep everything simple. The website should be intuitive and easy to navigate
- Make sure you can return to the homepage at any point
First iteration of the supplier order form
Final iteration of supplier order form
- Full Technical Report -
To view the full report, click the button below. It goes through each section discussed here in detail. It also includes extra information such as the employee function flow, the customer functional flow, different trends and various reports. It also discusses the user testing the website underwent.
- What I Learned -
- Taking a user-centred design approach to this project
- Learning to work with a client involves lots of communication to ensure the design is how they want it
- Iterative process. The website went through many design iterations until the final one was reached that the client was happy with
