SQL for Data Analytics · Testing the design

Testing the design

Putting Walgreens in

A good way to test a database design is to put real rows in it. If everything in the sales team's documents has a place to go, the design can hold what Edanava tracks. If something has nowhere to go, or has to be typed in twice, the design needs another look.

The box below starts from a full design for Edanava, with six tables: Clients, Contacts, TeamMembers, Opportunities, Steps, and OpportunitySteps. Its script already puts in the sales team and the five steps. Here is the example opportunity, to put in after them:

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

Add INSERT statements to the script for the Walgreens opportunity: its client, its two contacts, the opportunity itself, and its four steps. Use the design as it is. Write dates as '2022-10-19', the estimated revenue as 5000000, and NULL for a step that isn't done. Press Run to try your script.

Try your design

Run makes the tables your diagram describes, then runs your script on them. The script is saved with your diagram.

⌘/Ctrl + Enter
Show the SQL

Walgreens asks Edanava for a second project. Which tables get new rows for it, and which don't?

How I'd answer it

Opportunities gets one new row, with the same ClientID as the first Walgreens opportunity. OpportunitySteps gets a row for each step as it's assigned.

Clients and Contacts get nothing new. Walgreens and its two contacts are already there, once each. Steps and TeamMembers get nothing new either.

The design you built

This is the design you built over the last two lessons, put together in one place, with the names the lessons used. It's one good design for Edanava. There are others, and the next section shows where they could differ.

Edanava's opportunity tracker. Drag to move around, and use +, −, and Fit to zoom.

  • Clients has one row for each client, with its name, country, and address.
  • Contacts has one row for each person at a client, with a ClientID for the client they work at.
  • TeamMembers has one row for each member of the sales team, with their name and role.
  • Opportunities has one row for each deal in the making, with a ClientID for its client and a LeadID for the team member leading it.
  • Steps has one row for each of the five standard steps, with its number, name, and description.
  • OpportunitySteps is the joining table between opportunities and steps. It has one row for each step of each opportunity, with who it's assigned to and when it was completed.

A ? after a column's type means the column can be NULL. Every other column has to have a value in every row:

  • Every contact belongs to a client and has a name, a phone number, and an email, so the sales team can reach them.
  • Every opportunity has a client, a lead, a name, and the date it was created on.
  • An opportunity's estimated revenue and working notes can be empty, because a new opportunity may not have them yet.
  • A step's CompletedOn is empty until the step is done.
Where designs differ

The sales team's documents leave some things open. These are places where a reasonable design for Edanava's database could differ from the one in this unit's lessons.

  • Contacts. In the lessons' design, a contact belongs to a client. If each opportunity has its own contacts at the client, a joining table between Opportunities and Contacts records which contacts are on which opportunity, and Contacts still points at Clients.
  • The address. "200 Wilmot Road Deerfield" has the street and the city run together. Separate Street and City columns would let managers search opportunities by city.
  • Repeated steps. OpportunitySteps uses (OpportunityID, StepID) as its primary key, so each step happens at most once on an opportunity. If a step can be done again, like a second review of the statement of work, the table needs an ID of its own.
  • Free-text steps. A design could skip the Steps table and type each step's name into OpportunitySteps. Then the steps can change from one opportunity to the next, or be added on the fly, without settling on a fixed list first, which matters when a team's process isn't settled yet. The cost is that a misspelled or renamed step no longer matches the others, and each step's description has nowhere to go.

Edanava's application assigns team members to steps based on their role. Suppose only a Manager can do step 4, Review Statement of Work. Where could the design record that?

How I'd answer it

In the lessons' design, it can't. TeamMembers has an ID, Name, and Role, with the role as text. Steps has an ID, StepNumber, Name, and Description, and none of those says who can do a step. The rule would have to live in the application's code.

A Roles table would make room for it. Roles has an ID and a Name, with one row for each role. TeamMembers gets a RoleID in place of its Role text, and Steps gets a RoleID for the role that does that step. Then the database says that step 4 belongs to Managers, and the application can look it up.