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 …[Read more]