SQL for Data Analytics · Opportunities & steps

Opportunities & steps

Opportunities

Edanava's example opportunity, from the sales team's documents, without its contacts and steps:

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. …"

Lead Sales Person: Annabeth Chase

Opportunities get a table, Opportunities. The name, estimated revenue, created-on date, and working notes are details of the opportunity itself, so each one is a column.

The client already has a table, Clients. A client can have many opportunities, and an opportunity is with one client, so Opportunities gets a ClientID, a foreign key to Clients.

The lead sales person, Annabeth Chase, is on the sales team, and team members are in TeamMembers (ID, Name, Role). What does Opportunities store for the lead?

Drawing opportunities

The example opportunity, without its contacts and steps:

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. …"

Lead Sales Person: Annabeth Chase

An opportunity is with one client, and a client can have many opportunities. An opportunity has one lead sales person, from TeamMembers, and a team member can lead many.

The box starts from the design so far: Clients, Contacts, and TeamMembers.

Add a table for opportunities, with the details of the opportunity itself and a foreign key for its client and for its lead, and draw a line from each foreign key. Give every column a type and the table a primary key.

Opportunities has an ID, ClientID, LeadID, Name, EstimatedRevenue (DECIMAL), CreatedOn (DATE), and WorkingNotes (TEXT), with ID as its primary key.

One line goes from Opportunities.ClientID to Clients.ID, and one from Opportunities.LeadID to TeamMembers.ID.

The five steps

The sales team's document lists the steps every opportunity goes through:

  • 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

Every opportunity goes through the same five steps. How should the database store the steps themselves?

Steps has an ID, StepNumber, Name, and Description. The description is a sentence or two, so it can be TEXT.

The steps are already numbered 1 to 5. Why does Steps have both an ID and a StepNumber?

How I'd answer it

The StepNumber is the step's place in the order. If Edanava adds a step between 2 and 3, every step after it gets a new number.

The ID never changes, so everything that points at a step still points at the same one. If the steps were never going to change, the number alone could be the key.

Add a table for the five steps, with what the steps document lists about each one. Give every column a type and the table a primary key.

Steps has an ID, StepNumber, Name, and Description (TEXT), with ID as its primary key.

Steps on an opportunity

The example opportunity lists its steps so far:

  • 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 design so far has Opportunities and Steps, with one row in Steps for each of the five standard steps.

On the Walgreens opportunity, Create Cost Estimation was completed on 10/23/2022 and assigned to David Martinez. Where do that date and that assignment go?

The joining table

Opportunities and steps are a many-to-many relationship. An opportunity goes through many steps, and each step is used on many opportunities. A joining table between them, OpportunitySteps, has one row for each step of each opportunity, with two foreign keys:

  • OpportunityID, pointing at Opportunities
  • StepID, pointing at Steps

A joining table can have columns of its own, about one row of it. OpportunitySteps has two:

  • AssignedTo, a third foreign key, pointing at the team member doing the step
  • CompletedOn, a DATE that's empty (NULL) until the step is done

Here are the Walgreens rows, with Walgreens as opportunity 1, and the team members numbered in the order the sales team's document lists them (Annabeth Chase is 1, David Martinez 2, Si Wang-mu 3):

OpportunityID StepID AssignedTo CompletedOn
1 1 1 2022-10-21
1 2 2 2022-10-23
1 3 1 2022-10-24
1 4 3 NULL

Step 5, Get Signatures, has no row yet. A row goes in when a step is assigned to someone, so AssignedTo can be NOT NULL.

A joining table's primary key can be its two foreign keys together, or an ID of its own. Can (OpportunityID, StepID) be the primary key of OpportunitySteps?

How I'd answer it

Yes, as long as each step happens once on each opportunity. Then no two rows have the same opportunity and the same step, and the pair picks out exactly one row. In the Walgreens rows, OpportunityID 1 is on every row and AssignedTo 1 is on two, but each pair of OpportunityID and StepID is different.

If a step could happen twice on the same opportunity, the pair wouldn't be enough. Say management sends the statement of work back, and it has to be reviewed again. A second Review Statement of Work row would have the same pair as the first, and the primary key would refuse it. Then OpportunitySteps would need an ID of its own.

The steps document lists each step once, so this design uses the pair.

Drawing the joining table

The joining table has one row for each step of each opportunity, with who the step is assigned to and the date it was completed. Its primary key is the opportunity's ID and the step's ID together. It has three foreign keys: to Opportunities, Steps, and TeamMembers.

The box starts from the design so far: Clients, Contacts, TeamMembers, Opportunities, and Steps.

Add the joining table between opportunities and steps, with the pair of its opportunity and step foreign keys as its primary key, and draw a line from each of its three foreign keys. Give every column a type.

OpportunitySteps has OpportunityID, StepID, AssignedTo, and CompletedOn (DATE), with OpportunityID and StepID together as its primary key.

The lines go from OpportunitySteps.OpportunityID to Opportunities.ID, from OpportunitySteps.StepID to Steps.ID, and from OpportunitySteps.AssignedTo to TeamMembers.ID.

TeamMembers now has two lines to it, one from Opportunities and one from OpportunitySteps. What does each line say about a team member?

How I'd answer it

The line from Opportunities.LeadID says which opportunities a team member leads. Each opportunity has one lead.

The line from OpportunitySteps.AssignedTo says which steps a team member is assigned, on any opportunity. The two lines record different things, so both are needed. On the Walgreens opportunity, Annabeth Chase is on both:

Lead Sales Person: Annabeth Chase

  • Confirm Opportunity: completed on 10/21/2022, assigned to Annabeth Chase
  • Create Statement of Work: completed on 10/24/2022, assigned to Annabeth Chase