Big Picture Question - SQL and JSON

This is a challenge we would like to open to this community for comments. TL;DR, the question is, using Yii2 what is a clean method to easily add fields to an existing DB TABLE, WITHOUT updating server side code or database schema? And without converting to a complete JSON DB like MongoDB.

Simple use case -> Standard Purchase Order Table (PO) with a one to many relationship with a Line Item (LI) table.

There is a standard PO_TABLE with a 1 to many relationship to the LI_TABLE, with your typical fields











We would like the customer (no access to the codebase) to be able to add custom fields onto the PO Object and Line Item Object. These must be stored in the DB and as this is a web app and everything is being pumped into JSON for REST services, keeping the data in a json format seems logical.

Reasons and Observations.

  • Have done this prior with an ‘extension’ table that essentially holds: ID, TableName, FieldName, FieldType and VALUE, then a query pulls the extension data in with the primary data. So for a new value on the PO it would look like

1,PO_TABLE,Customer_Attr1,String,Hello World

The extension tables get big and ugly.

When ever a query to the PO tables happens, it was done via a view or code that pulls in the extension fields.

  • Why now? see SQL example below, with the new JSON aware Databases, there is probably a better approach today.

  • Yii seems great when having a defined Schema in the DB already, but are looking more to a schema on read approach.

  • With the new legit JSON field types in MYSQL and PostreSQL, we want to take advantage of these

  1. We want a dynamic set of the fields that are controlled by the customer. In this instance it will not extend to table relationships.

  2. We do NOT want to go through an build cycle (Dev, QA, Prod) every time a field is added

2a. We do NOT want to touch main code branches or have to go through SQL table UPDATES between versions(no no no)

2b. Having a framework behind the scenes that is built to do this via configuration is ok (this is the way we’ve done it before - but it was a lot of code, think essentially the Java Spring Framework before it was even born) Not looking to recreate this in PHP.

  1. We understand all of the performance, error handling, validation etc. challenges, but the dynamic nature is more important than performance here.

  2. Why not a DB like MongoDB? We want to leverage the HUGE amount of SQL data, knowledge, tools, skills etc. that we already have and not go pure JSON DB (e.g. mongoDB) We need referential integrity and third normal form in areas of the product.

I know this is a big question. If interested in this topic, OOB discussion is ok.

This example below has

In this Example, TWEETS is the Table that has two fields tweet and created_at

the tweet field is a JSON field and created_at is a standard SQL date field, the table looks,acts and tastes like a standard SQL table.

select extract(‘day’,created_at) janday,count(*) cnt

from t,

 -- unnest a tweet on the hashtags of each entities

 lateral flatten (input=> t.tweet,'entities.hashtags')tags,

 (select distinct ph_hashtag




    where p_name ='Blue Sky'

    and   p_productkey = ph_productkey) p

 where tags.value:text::string = p.ph_hashtag

 and   created_at >= '2014-01-01 00:00:00'

 and   created_at >= '2014-02-01 00:00:00'

group by 1

order by 1

If you can’t use JSON or touch schema you still have two options.

  1. TEXT w/ JSON inside as string. You can’t search or filter by it except by using Elastic or Sphinx which, I guess aren’t the option if you can’t even use JSON fields.

  2. EAV. Entity Attribute Value. What you’re calling “extension tables”.

I would LOVE to use JSON, hence the wanting to take advantage of the new JSON features in DBs like MySQL and PostreSQL. I think it’s the key, being able to combine the unstructured JSON data along with the structured data. Prior was sticking JSON in a BLOB/TEXT field, now there are better types available in most DBs. I see that Yii, generally, takes the DB model to create the Model from the DB… looking to perhaps enhance this so that it can also take into account a JSON structure. TBH, this was one advantage of XML where you could create a DTD.

Funny I haven’t heard the term EAV, but have used the method alot. :)