mysql : 5.6 java : 11
Am in need of an mechanism to store unschematised data in my db. the case is as follows.
Objective :
To get all user from a 3rd party app and store it in db.
Problem facing :
since the attributes of different apps are different. Example(some may have an attribute IS_ACTIVE some doesnt have this and have some other like IS_DELETED which is not available in previous app)thus cannot make a table to store all user of unknown attributes.
Methods tried
Method 1 : Entity Attribute Value model
1.1 i will have a table for entities with values like ({id : 1, entity : customAppOne})
1.2 a table for attribute like ({aid : 1, attrName : "name", inbound : "name", outbound : "userName"})
1.3 and finally a table for value like ({entityId : 1, aid : 1, value : "testUserName"})
drawbacks faced :
instead of storing user data like ({name : testUserName, age : 2, mail : [email protected]})
Its storing each values in multiple rows like
[{entityId : 1, aid : 1, value : "testUserName"},
{entityId : 1, aid : 2, value : "2"},
{entityId : 1, aid : 3, value : "[email protected]"}]
thus in cases with 1 million users with 20 different attributes the table size becomes 20x
Method 2 : placebo columns
will have 2 table one for attribute and another for value
The attribute table will be like ({aid : 1, attrName : "testName", columnName : "col2"})
the value table schema will have 100 columns with each of them named “col1″,”col2″,”col3”,… “col100”
and the value table will be like
{id:1, col1 : null, col2 : "testName", col3 : null} (will be null for rest 97 column
Drawbacks
This solves the issue of unknown column name as well as single row entry but as usage of 100 columns just because we dont know the amount of parameters and name of parameter seems costly also considering the difficulties in querying.
Method 3 : JSON
since mysql supports JSON tried putting all the user data as json and storing directly in the table and since i can query and do CRUD operation with JSON functions provided by mysql i dont need to rewrite the whole thing this solving all problems
Drawbacks
The main drawback is the db framework that am using does not support JSON although if i find alternative(which is not possible to change just for this) consider the query to update minor status where age > 18. unlike a column where only the specified column being searched in json all keys will be searched for all user which is sooo innefficient. Am out of ideas now can someone help me out?