SQL for Data Analytics · The Edanava problem

The Edanava problem

Edanava

The lessons in this unit walk through designing one database, for a company called Edanava, one decision at a time. The diagram boxes check each piece as you build it.

The problem is roughly based on a real application I was asked to design and build during my first software development internship.

Edanava is a multinational digital consulting company. Other large companies hire Edanava to build software for them or for their customers. At any time, Edanava has thousands of sales opportunities with businesses that might hire it for a project. A sales opportunity is a deal in the making. It's how the company tracks the early stages of a deal, before the contract is signed and the work begins.

Edanava has no software for tracking its opportunities. Someone on the sales team puts each new opportunity in a Word document, and nothing says how the document is laid out. The documents are emailed back and forth, so different people end up with different versions of the same one. Managers can't search the opportunities or see how many are closed or still in progress. Nothing reminds anyone to follow up on an opportunity they've forgotten about. The company estimates it loses more than $8 million a year in possible revenue to opportunities that were delayed or forgotten.

So Edanava is building a web application where the sales team tracks every opportunity in one place. Salespeople create opportunities and add information to them. The application assigns team members to the steps of each opportunity, based on their role on the team, and sends a reminder when too much time has passed since the last step was finished. Managers can see analytics about the opportunities.

You're the lead backend developer on the project. Your job is to design the database the application needs.

The sales team's documents

The sales team has given you three documents, to show the kind of information the application has to track. Everything in them has to be in the database, with the right relationships.

An example opportunity

Client: Walgreens, United States, 200 Wilmot Road Deerfield

Opportunity Name: Appointment Tracking App for Walgreens

Estimated Revenue: 5 million

Created On: 22-10-19

Working Notes: "Walgreens needs new software to help customers sign up for and manage appointments at their pharmacy. I spoke to Mrs. Blight last week to confirm the project was viable. Our margins for the cost estimation are good and we've created the statement of work. We need to get the statement reviewed by management before sending it over to the client."

Client Contacts:

  • Jessica Whitwell, (111) 222-3333, jessica@wallgreens.com
  • Amity Blight, (222) 333-4444, amity@wallgreens.com

Lead Sales Person: Annabeth Chase

Steps for this opportunity:

  • Confirm Opportunity: completed on 10/21/2022, assigned to Annabeth Chase
  • Create Cost Estimation: completed on 10/23/2022, assigned to David Martinez
  • Create Statement of Work: completed on 10/24/2022, assigned to Annabeth Chase
  • Review Statement of Work: no completion date yet, assigned to Si Wang-mu

Steps in an opportunity

  • Step 1 - Confirm Opportunity: Follow up with the client to confirm the opportunity
  • Step 2 - Create Cost Estimation: Estimate the cost of completing the project and identify profit margins
  • Step 3 - Create Statement of Work: Create Statement of Work document that outlines everything that will need to go into the project
  • Step 4 - Review Statement of Work: Review statement of work with management
  • Step 5 - Get Signatures: Get sign off from client

Current sales team members

  • Name: Annabeth Chase, Role: Sales Representative
  • Name: David Martinez, Role: Sales Representative
  • Name: Si Wang-mu, Role: Manager
  • Name: Duncan Idaho, Role: Sales Representative

Read the three documents as data to track. What are the things Edanava's database has to keep track of? Which of them would you make tables?

How I'd answer it

The things are the clients, the contacts at each client, the sales team members, the opportunities, and the five steps.

Each of those five gets a table.

There's one more thing, which is easy to miss. For each step of each opportunity, the database has to know who it's assigned to and when it was completed.

Clients

The example opportunity starts with its client:

Client: Walgreens, United States, 200 Wilmot Road Deerfield

Client Contacts:

  • Jessica Whitwell, (111) 222-3333, jessica@wallgreens.com
  • Amity Blight, (222) 333-4444, amity@wallgreens.com

The client line has three details in it: a name, a country, and an address. A client has details of its own, so clients get a table: Clients, with an ID, Name, Country, and Address.

Each contact has details of their own too: a name, a phone number, and an email. Contacts get a table, Contacts, with an ID, Name, Phone, and Email. It also needs a foreign key, to say whose contact each one is.

In the example, the contacts are listed on the opportunity. Which table should each row in Contacts have a foreign key to?

Drawing clients and contacts

A client can have many contacts, and each contact works at one client. In Contacts, a ClientID says which client.

Client: Walgreens, United States, 200 Wilmot Road Deerfield

Client Contacts:

  • Jessica Whitwell, (111) 222-3333, jessica@wallgreens.com
  • Amity Blight, (222) 333-4444, amity@wallgreens.com

Draw a table for Edanava's clients and one for their contacts, with what the example opportunity tracks about each. Give every column a type and each table a primary key, and draw the line between the two tables.

Clients has an ID, Name, Country, and Address.

Contacts has an ID, ClientID, Name, Phone, and Email.

ID is each table's primary key, and the line goes from Contacts.ClientID to Clients.ID.

How many rows does the Walgreens example put in each of your two tables, and what's in the ClientID column?

How I'd answer it

Clients gets one row, for Walgreens. Contacts gets two, one for Jessica Whitwell and one for Amity Blight.

Both rows in Contacts have the same ClientID: the ID of the Walgreens row.

The sales team

The sales team document lists each member's name and role:

  • Name: Annabeth Chase, Role: Sales Representative
  • Name: David Martinez, Role: Sales Representative
  • Name: Si Wang-mu, Role: Manager
  • Name: Duncan Idaho, Role: Sales Representative

Team members get a table of their own. A role is text, like Manager.

The box starts from a design with Clients and Contacts already in it.

Add a table for the sales team, with what the sales team document lists about each member. Give every column a type and the table a primary key.

TeamMembers has an ID, Name, and Role, with ID as its primary key.

TeamMembers has no lines to anything yet. Which of the three documents connects team members to the rest of the design, and how?

How I'd answer it

The example opportunity does, in two places:

Lead Sales Person: Annabeth Chase

Steps for this opportunity:

  • Confirm Opportunity: completed on 10/21/2022, assigned to Annabeth Chase
  • Create Cost Estimation: completed on 10/23/2022, assigned to David Martinez
  • Create Statement of Work: completed on 10/24/2022, assigned to Annabeth Chase
  • Review Statement of Work: no completion date yet, assigned to Si Wang-mu

The opportunity has a lead sales person, who is a team member. And each step of the opportunity is assigned to a team member.