I want to give you tools that you can use for building databases that can handle complex relationships. In the previous article in this series, I looked at hierarchical data. The classic example of a hierarchy is an org chart. On most org charts I’ve seen, an employee has only one boss, which is a one-to-many relationship. The other kind of segmentation is many-to-many. A good instances of this is your social circle. Most people have many friends, and those friends have multiple friends themselves.
This is a common pattern. When developing a multi-tenanted application, users will often want to see and update information for multiple tenants. Imagine, if you will, a freelance Web developer. He’ll set up Google Analytics accounts for each client’s website, and he’ll add both the client and himself as users to the account. There’s multiple users, and there’s multiple websites.
Create Table Websites ( WebsiteID Int Auto_Increment Not Null Primary Key ,Url Varchar(200) Not Null ); Create Table Users ( UserID Int Auto_Increment Not Null Primary Key ... ); Create Table WebsiteUsers ( WebsiteID Int Not Null ,UserID Int Not Null ,Constraint WebsiteUsers_pk Primary Key (WebsiteID, UserID) ,Constraint WebsiteUsers_fk_WebsiteID Foreign Key (WebsiteID) References Websites(WebsiteID) ,Constraint WebsiteUsers_fk_UserID Foreign Key (UserID) References Users(UserID) );
The third table, WebsiteUsers, is where the model gets interesting. This table is called by various names like join table or bridge table, but regardless of its appellation, it is used to represent a many-to-many relationship. If we put a UserID on the Websites table, only one user could have access to that website and likewise, if we put a WebsiteID in the Users table, a user could only access one website. So, we create a separate table that represents their relationship and allows many users to access a website, as well as allowing a user to administrate multiple websites. Let’s look at a query that gets all the websites for a user:
Select Url From Websites Join WebsiteUsers Using(WebsiteID) Join Users Using(UserID) Where UserID = @UserID
There’s a slight twist on the many-to-many concept. Instead of creating relationships between friends or users and websites, you instead use a category to segment your data. You could call this bridge a tag, a segment, or a group. Think of organizing your photos. You might tag a picture with honeymoon, sunset, or Hawaii. When you go back to search for Hawaii photos, you’ll find them all even if they were taken on your honeymoon or a business trip.
A SaaS application will often use a similar concept for features. One plan has features A and B and another has C and D. To get back to photos, think of a stock photography website. It has categories for people, still life, landscapes, business, and home. Alice and Bob are two pretend graphic designers. Alice only needs landscapes and home while Bob needs both people and business. Notice that these categories can overlap: a picture can be in the category people and in business. Let’s create a model for this situation.
Create Table Photos ( PhotoID Int Auto_Increment Not Null Primary Key ,PhotoName Varchar(50) Not Null ,DateTaken DateTime Not Null ... ); Create Table PhotoCategories ( Category Varchar(50) Not Null ,PhotoID Int Not Null ,Constraint PhotoCategories_fk_ PhotoID Foreign Key (PhotoID) References Photos(PhotoID) ,Constraint PhotoCategories_fk_Category Foreign Key (Category) References Categories(Category) ) Create Table Categories ( Category Varchar(50) Not Null Primary Key ); Create Table UserCategories ( Category Varchar(50) Not Null ,UserID Int Not Null ,Constraint UserCategories_pk Primary Key (Category, UserID) ,Constraint UserCategories_fk_Category Foreign Key (Category) References Categories(Category) ,Constraint UserCategories_fk_UserID Foreign Key (UserID) References Users(UserID) ); Create Table Users ( UserID Int Auto_Increment Not Null Primary Key ... );
The PhotoCategories table puts pictures in categories. The UserCategories allows users to be dropped into a category which then grants them access to the photos that are also in that category. This is a little more complicated pattern: instead of a relatively simple many-to-many relationship, this is many-to-many-to-many (photos to categories to users). However, when you need it, you need it.
I’ve show you how to implement a many-to-many design. In the next post, I’ll talk about those anti-patterns you should watch out for when designing a multi-tenanted database. Sign-up to receive a notification when it’s ready!
Want to hear more about what I’ve learned about designing databases in MySQL? You can subscribe below and I’ll send you updates on a new book I’m writing that helps you navigate past the gotchas in MySQL. If you sign-up, I’ll also send out stuff on MySQL like tips and tutorials.