The sales database has been created using MySQL version 8 and consists of 12 tables and 10 stored procedures. A diagram of the relationships between the tables is shown below together with a description of the usage of each item
The following list outlines the purpose of each data table.
| Table Name | Description |
| tbproduct | Holds product information |
| tbproductsupplier | Links product to supplier |
| tbpurchaseorder | Supplier purchase orders |
| tbreportsetting | Report configuration |
| tbsale | Records sales header data |
| tbsale_reporting | Reporting subset of sales table |
| tbsaleline | Sales line data |
| tbsaleline_reporting | Reporting subset of sales line |
| tbstocksilo | Groups stock received from supplier |
| tbstore | Store information table |
| tbsupplierinvoice | Invoices received from suppliers should match with purchase orders |
| tbunknownprodsup | Default value for reports if supplier yet to be allocated to a product |
Stored procedures are used to manipulate the data and are called by various routes and jobs within Talend. In the proof of concept they are called by the root user but in a practical environment this would be a considerable security risk and a database user would be created specifically for this purpose with sufficient privileges for the reqiuired tasks only.
| Procedure Name | Description |
| AllSalesByRegion | Get sales totals from reporting tables grouped by region and store. |
| CheckAssignedSupplier | Checks if a product has been assigned to a supplier, returns 1 or 0. |
| CheckProductSupplierValues | Checks if a supplier and product exist for given parameters, returns 1 or 0. |
| CreateProductSilo | Create a new product silo for a given product code, purchase order number and order quantity. |
| GetProductSupplier | Gets the supplier from a given product code or uses the default table if none have been assigned. |
| GetSalesByRegion | Gets sales by store code by product for the defined region using the reporting tables. |
| ProcessSales | Gets cost price from active silo and decrements quantity, returns yes or no depending if reorder level is reached. |
| RaisePurchaseOrder | Create a purchase order record for a supplied product. |
| SupplierInvoice | Create a supplier invoice record from a purchase order. |
| UpdateReportSetting | Update the report configuration table and load sales reporting delta for the day. |
SQL scripts to build the database can be downloaded from GitHub
- RetailPOCDatabase.sql – Database build script
- RetailPOC_SQLData.sql – Sample data script
- RetailPOC_CleanData.sql – Clear data script
Run the build script to create the database followed by the data script to load in sample data. To clear data and reset the system run the third script followed by the data load.
If you don’t have MySQL available the community edition can be downloaded here.
