Scenario

A coffee company was looking to expand nationally by opening a number of franchise locations. At the time, their data resided in multiple different systems: CRMs, suppliers' databases, accouting softwares and spreadsheets. 

Their goal was to improve operational efficiency by redesigning the entire data infrastructure, making it easier for their executives to make data driven decisions.

Solution

First, I reviewed the existing data and made a list of all entities and their attributes. After defining the relevant entities and attributes for the new system, I created an Entity Relationship Diagram (ERD) using pgAdmin. 

Due to the redundancy of the data structure as observed in the ERD, I normalized multiple database tables to conform with second normal form (2NF). This reduces redundant data and is preferred for Online Analytical Processing (OLAP) applications, which aligns with the stakeholders' objective of making data driven decisions. 

After normalization, I defined the primary keys and relationships between the tables in the ERD.

Occassionally, the marketing team, external consultants and other parties either requires access to the database or requests specific queries. To limit access to sensitive data and to simplify data retrieval, I created appropriate views to return requested information.

This information can then be exported in different formats and uploaded to different MySQL and IBM db2 databases.

Outcome

By harnessing insights from a centralized database, the coffee company has achieved a deeper understanding of its business operations. Their data science and analytics teams built advanced analytical models and dashboards. 

This helps the company accurately track and compare key performance indicators, while expanding into new locations.

Related Experiments

• Data pipeline with Google cloud ↗

• Distributed training with TensorFlow ↗