Over the weekend, I took my first look at the JSON column type in MySQL 5.7. The JSON column type introduces a Document-like feature-set on top of the traditional relational database framework. In that post, I looked at storing Entity-Attribute-Values (EAV) in a JSON field. In this post, I wanted to look at an approach that we use at InVision in which an “invitation” to the system can be associated with Prototypes and Boards. Currently, we maintain these associations with a comma-delimited list of IDs (which is a garbage approach); and, I wanted to see if it would feel less like garbage if I used a JSON column type. To explore this, I’m using MySQL 5.7.32 and Lucee CFML 5.3.7.47.

CAUTION: This Should Have Always Been a Normalized Set Of Tables

With InVision, when you invite a user to the platform, you can start associating the pending invitation with other entities in the system such that when the recipient ultimately accepts the invitation, they are automatically associated with each of the aforementioned entities. Today, this is implemented in a rather naive fashion in which foreign entity IDs are stored as a comma-delimited list in the invitation record. Imagine a table that looks something like this (truncated):

• email - The recipient of the invitation.
• prototypeIDList - The comma-delimited list of prototypes associated with the invite.
• boardIDList - The comma-delimited list of boards associated with the invite.

This type of structure leads to all sorts of problems:

• We run into field-length overflows when too many IDs are added to the list.
• We have to parse the ID-list as a String when searching for associations.
• We can’t index the IDs.
• We end up leaving “dead IDs” in the various lists because it’s overly complicated to remove IDs when the relevant entities are deleted.

#sql #mysql #json #lucee