SQL for Data Analytics · Testing the design
Testing the design
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.comLead 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.
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.
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.
Clientshas one row for each client, with its name, country, and address.Contactshas one row for each person at a client, with aClientIDfor the client they work at.TeamMembershas one row for each member of the sales team, with their name and role.Opportunitieshas one row for each deal in the making, with aClientIDfor its client and aLeadIDfor the team member leading it.Stepshas one row for each of the five standard steps, with its number, name, and description.OpportunityStepsis 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
CompletedOnis empty until the step is done.
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
OpportunitiesandContactsrecords which contacts are on which opportunity, andContactsstill points atClients. - The address. "200 Wilmot Road Deerfield" has the street and the city run together. Separate
StreetandCitycolumns would let managers search opportunities by city. - Repeated steps.
OpportunityStepsuses(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 anIDof its own. - Free-text steps. A design could skip the
Stepstable and type each step's name intoOpportunitySteps. 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.