Distributor Database Design
The following project was created for the Systems Analysis course at Howard University.
Mr. Johnson is the owner of a footwear distributorship whose customer base has dramatically increased beyond the capabilities of his current system. I’ve acted as Mr. Johnson's systems analyst to modernize his business and give him competitive advantage in his market.
He identified the following goals he would like to be able to accomplish with the new system:
-
Send customers mail order promotions based on their targeted product interests
-
Keep track of which inventory items sell quickly and slowly
-
Easily access customer payment status
Role / SQL, Database Design, Project management
Task / Modernize the database system of a shoe distributor
Duration / 3 weeks
Tools / Command Prompt, Atom, Figma
System Initiation
System Initiation is the first step in the systems development process and arguably the most crucial as it details the business problems, scope, and goals. Mr. Johnson highlighted 3 main problems that are contributing to his inefficiencies:
​
Problem 1 / Can't keep track of existing customers
Problem 2 / Can't keep up with new customers
Problem 3 / Can't keep track of inventory and order stats
System Analysis
Once the goals and problems were identified, I came up with technical solutions using database technology to automate his business, and conduct a cost-benefit analysis of implementing a new system.
Solution 01 /
Update and add relevant info for dealing with customers.
This solution manifests as an extensive series of attributes so all customer information is in one location. Mr. Johnson will be able to look customers up by name, ID, and new product interests. This means he will finally be able to send out mail order promotions of items customers have expressed interest in.
Solution 02 /
Include each product’s total stock count, stock on hand count, and date received in inventory.
By adding these figures into the database, Mr. Johnson will be able to see the rate at which his stock is selling so he can make informed decisions like what excess stock he should get rid of, or what products are popular and require back-ordering.
Solution 03 /
Add additional data tables to support his accounting and supply chain business functions.
This will allow Mr. Johnson to identify overdue accounts so he can request payment and make sure customers' orders are being fulfilled in a timely manner. These additional tables require referential integrity constraints so customers’ and Mr. Johnson’s data stays connected.
Costs
Expensive: a system designer's contractor fees, software and hardware integrations, and system maintenance will likely cost around $70,000 (one-time fee)
Time-consuming: it could take several months until Mr. Johnson realizes the benefits of this new system because his current customer base requires updating, additional relational tables are needed, as well as training of the new system
Benefits
Increased revenue: automating the current system will make it possible to handle more customers, orders, and inventory likely leading to an increase in revenue
Establishing brand loyalty: this new system will improve the experience of existing customers, encouraging them to continue buying from Mr. Johnson rather than a competing distributorship​​​​​​​
​
System Design
This phase of the systems development life cycle (SDLC) includes the actual system design, including attributes, primary and foreign key identification, creating and populating tables, and writing sample queries to ensure the system's functionality. Ultimately, we need to fully understand how the entities will work together to create meaningful insights.
Learnings
The order of tables matters
01
I quickly learned that my primary and foreign key restraints were ineffective if I was referencing a table that didn't exist yet.
Understanding this improved my SQL workflow and organization.
​
Be intentional with queries
02
No amount of complicated code will hide if your queries aren't useful to the problem you're trying to solve.
Referencing the problem statement and goals helped me to keep the big picture in mind allowing me to write more meaningful queries.
When in doubt, draw it out
03
Whenever I felt stuck after encountering an issue with a query result or referential integrity constraint error, drawing a simple relational diagram of the data flow always led me to right result.
​