Published on: Mar 4, 2019

JSON columns in MySQL

One of the contract projects I work on has multiple types of products - one of those types is a subscription product. When a user purchases a subscription product a bunch of stuff happens behind the scenes, those steps aren't really important right now. The client wanted to split the subscription type into two new types: social subscriptions and flexible subscriptions.

The first thought that came to mind was to simply create two new types and forget about the non-specific subscription type - but, at the end of the day they would still be subscriptions right?

Thankfully the products table had a JSON column named metadata - so my solution was simply to add a subtype key to subscription products equal to either social or flexible.

/**
 * Check if the product is a subscription
*/
public function isSubscription() 
{
    return $this->type == self::Subscription
}

/**
 * Check if the product is a "social" subscription
*/
public function isSocialSubscription() 
{
    return $this->isSubscription() && $this->meta->subtype === 'social';
}

/**
 * Check if the product is a "flexible" subscription
*/
public function isFlexibleSubscription() 
{
    return $this->isSubscription() && $this->meta->subtype === 'flexible';
}

JSON columns are great to save metadata about certain records without having to create a specific column for the given data - I've even used them to store the state of a object at a certain time.

As with everything, be careful to overuse them because they can quickly fill your database with useless information.

🤠