Data Project
The purpose of this set of web pages is to demonstrate knowledge of building Microsoft Excel based solutions, data structure design and live querying on data tables (MySQL and MS SQL).
SQL Queries Overview
The Main Overview page gives an outline of requirements for Widge-It Ltd. As carried out in the Microsoft Excel solution, the data has been separated into three sets of tables, namely: customers, Widge-It Ltd sales staff and transactions. This will cut down on the amount of data stored in the database. For the purpose of this live database-driven exercise, the tables have been named as:
- customers
- staff
- transactions
This is a one to many design, where Customers and Staff contain one record per contact but Transactions can hold as many references to Customers and Staff as required.
Note that this exercise is not going to cover the creation of a user interface for administration of the system. Also please bear in mind that this is not a full production database implementation. A full implementation will have a greater number of tables and would allow multiple contacts per company, multiple contact methods etc.
There are some differences between an Excel solution and a database solution, so these differences will be explained through the process.
Fields
There are a number of columns that were created for the Excel solution to aid the user in filtering data, which are not necessary as fields in a database solution. For the transactions table, these are:
- Week Commencing
- Day
- Week
- Month
These are not needed as they are purely the result of calculations created from the source 'date' field that exists in the Transactions table. If this data needed displaying separately in a database form, then it would be advisable to build this data live from the source date field and display it in a way that it could not be edited.
For the same reason, the following data would not need storing in the Transactions table as these are also calculations based on existing fields.
- Total Widget Sales
- Total Widgets Sold
- Total Sales
Tables
Customers table
The Customers table will hold records of all customers. To simplify matters, the data is to be structured so that there is one customer contact name included in each customer record. In a full CRM system, there would likely be a different structure to allow for more contact names. The table will need a primary key to identify each unique customer, so that they could be linked to transactions. The primary key is named 'cust_id' in the table.
Staff table
The Staff table will hold records for all staff members of Widge-It Ltd. The primary key for this table will be a unique staff ID number. This is named 'staff_id' in the table.
Transactions table
Transactions will hold the sales data, which will link between the customers and staff tables.
The primary key for this table is 'trans_id'. This will be a unique ID.
The foreign keys within this data are 'trans_cust' and 'trans_staff'. The 'trans_cust' is linked to the customer table, while the 'trans_staff' is linked to the staff table.
The primary and foreign keys will be linked as per the screenshot below:
Structure
All fields
The structure complete with the other fields looks like this:
This is a one to many design, where Customers and Staff contain one record per contact but Transactions can hold as many references to Customers and Staff as required.
Click the button below to advance to the next stage
Next: >> Simple Table Lists
Or use the above 'SQL Queries' dropdown menu to jump to any stage.