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
PO.ID
PO.NUMBER
PO.DATE
etc.
LI.POID
LI.ID
LI.NUMBER
LI.ITEMID
LI.ITEMQTY
etc.
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
-
We want a dynamic set of the fields that are controlled by the customer. In this instance it will not extend to table relationships.
-
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.
-
We understand all of the performance, error handling, validation etc. challenges, but the dynamic nature is more important than performance here.
-
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
twitter.data.tweets t,
-- unnest a tweet on the hashtags of each entities
lateral flatten (input=> t.tweet,'entities.hashtags')tags,
(select distinct ph_hashtag
from
sales.public.producthashtags,
sales.public.product
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