In the previous article, we learned how you can design your database schema so that many customers can share one database and one set of tables. The examples I used were fairly simple: they consisted of single tables or simple hierarchies like the order and line item tables. In those examples, you had a single tenant id that segmented the tables. Now I’d like to introduce the idea of hierarchical segmentation. This is a pretty big term for the idea of allowing customers to see data based on where they are in a hierarchy, like an org chart.
Let’s say you have three sales teams each with its own manager for three regions in the US: East, Central, and West. A salesman in the East only gets to see his own prospects, while the East sales manager sees the prospects of the entire East sales team. At the very top, the VP of US Sales can access the prospects for all three regions. If we were to design that schema, it might look something like what you’ll find below.
Let’s start off with the Users table. It’s pretty basic, and I don’t think you’ll have trouble understanding it.
Create Table Users ( UserID Int Auto_Increment Not Null Primary Key ,FirstName Varchar(100) Not Null ,LastName Varchar(100) Not Null … );
We now go to the Prospects table. This is where we store the leads that salesmen use.
Create Table Prospects ( ProspectID Int Auto_Increment Not Null Primary Key ,SalesRepUserID Int Not Null ,CustomerName Varchar(100) Not Null ,ContactName Varchar(100) Not Null ,ContactPhone Varchar(10) Not Null ,PotentialRevenue Decimal(10,2) Not Null ,Stage Varchar(50) Not Null ,Constraint Prospects_fk_SalesRepID Foreign Key (SalesRepUserID) References SalesReps(SalesRepUserID) );
Notice the column SalesRepUserID. When our salesmen come in to work, they fire up their computers, log into their customer relationship manager app, which runs a query like this:
Select CustomerName, ContactName, ContactPhone From Prospects Where SalesRepUserID = @LoggedInUserID;
The next table maps the user id of the sales reps to a region. Notice that we can only have one region per sales rep since SalesRepUserID is a primary key. The user id must be in the SalesReps table before the user can be assigned a prospect, since the SalesRepUserID column in Prospects has a foreign key to the SalesReps table.
Create Table SalesReps ( SalesRepUserID Int Not Null Primary Key ,RegionID Int Not Null ,Constraint SalesReps_fk_RegionID Foreign Key (RegionID) References Regions(RegionID) ,Constraint SalesReps_fk_SalesRepUserID Foreign Key (SalesRepUserID) References Users(UserID) );
The next table is the Regions table, which declares who the regional manager is.
Create Table Regions ( RegionID Int Auto_Increment Not Null Primary Key ,RegionName Varchar(50) Not Null ,RegionGroupID Int Not Null ,ManagerUserID Int Not Null ,Constraint Regions_fk_ManagerUserID Foreign Key (ManagerUserID) References Users(UserID) );
The East regional manager would log in, and the CRM app would run a query like this:
Select FirstName, LastName, Sum(PotentialRevenue) As PotentialRevenueSummed From Prospects Join SalesReps Using(SalesRepUserID) Join Users On UserID = SalesRepUserID Join Regions Using(RegionID) Where ManagerUserID = @LoggedInUserID Group By FirstName, LastName ;
Next comes the RegionGroups table. This stores the labels for the regional groups, like North America or EMCA, as well as the vice president who oversees that region group.
Create Table RegionGroups ( RegionGroupID Int Auto_Increment Not Null Primary Key ,RegionGroupName Varchar(50) Not Null ,VpUserID Int Not Null ,Constraint Regions_fk_VpUserID Foreign Key (VpUserID) References Users(UserID) );
When the vice president logs in, the CRM app run a report that shows the potential revenue by region:
Select RegionName, Sum(PotentialRevenue) As PotentialRevenueSummed From Prospects Join SalesReps Using(SalesRepUserID) Join Users On UserID = SalesRepUserID Join Regions Using(RegionID) Join RegionGroups Using(RegionGroupID) Where VpUserID = @LoggedInUserID Group By RegionName ;
So, in summary, we can see the hierarchical relationship: prospects are owned sales reps, who are under managers, who are overseen by vice presidents. Here’s a Venn diagram of the ownership.
The schema reflects these real-world hierarchies: the Prospects table is segmented by the SalesRepUserID. Each row in the SalesRep table is segmented by a RegionID and thus a ManagerUserID in the Regions table. Finally, the Regions table is segmented by a RegionGroupID from the RegionGroups table which has its own VpUserID.
Besides modeling an org chart, hierarchical segmentation can also be used to represent companies that have independent subsidiaries or business units. The parent company needs access to the data of the subsidiary, but the subsidiary only gets to see its own data. Generalizing this idea, we see that users can access all the data of those users directly under them in the hierarchy. We don’t need to add a ManagerUserID to the Prospects table, because each row in Prospects has a SalesRep row which in turn is tied to a Region row which declares the ManagerUserID. When a manager is fired, we can quickly change the ManagerUserID in the Regions table without touching any of the rows in Prospects.
You should use this hierarchical pattern when users get access data in your app based on their position in a hierarchy. Beyond complying with the rules of data normalization, it also makes access control much easier. I’ve seen workarounds for databases that needed a hierarchy that included creating separate users for the regional manager for each branch office. That’s painful.
The next post will cover modeling many-to-many segmentation in your database.
Want to hear more about database design, specifically with MySQL? Well, I’m writing a book! You can subscribe below and I’ll send you updates. I’ll also send out stuff on MySQL like tips and tutorials.