Other MySQL, different type of data in a field

[udg]

Hi all,

I'd like to read your suggestions about how to properly reference data of very diverse nature in a table.

I have a main table which relates 1-1 with some specific info. The problem is that this info varies greatly from record to record, altough there are a bunch of predefined types.

I thought about two apprroaches and I'd like to hear your opinion.

Solution #1
Add to Main table two fields : type (numeric o text) and vdata (BLOB).
Use type to identify the kind of data stored in vdata (in the form of a Map object?).
It will go like this:
Main
ID - autoinc....
blahblah - fields already in Main
type - int (e.g. 15)
vdata - BLOB (a map related to type 15 above)

So reading record "X" I will know that its vdata is of type=15 (e.g. personal data) and work on the Map object read from vdata accordingly. For a different record I could read type=7 (e.g. train data) and again I could treat the Map object as related to trains instead of to humans.

Solution #2
Add to Main table two fields: fk_type_id and fk_data_id
fk_type_id will point to a record in a "lookup table", App_type, where I store the definition of every possible type managed by my sw, along with the table name associated to it.
fk_data_id will point to a single record in the dedicated table whose name is referenced through fk_type_id above.
Something like:

App_type
ID - autoinc..
name - varchar (e.g. human, train, room.., or even 15, 7, 8 as in Solution #1)
table_name - varchar (e.g. HumanData, TrainData..)

Then for each type I define a specific table. As an example:
HumanData
ID - autoinc
name - varchar
surname - varchar
phone_number - ..

RoomData
ID -autoinc
floor -
number -
....

Either solution seems to fit. Solution2 sounds more flexible, while Solution1 appears easier.
What would you suggest? Solution3 maybe? TIA

Update: I should add that searching on the "specific" data won't be a requirement

udg

 

[OliverA]

Solution #1 plus a lookup table (does this make it #3?) for the type (columns: typeid, typedescription) if needed (for picking a type in descriptive form or even to document your types somewhere).

Welcome to EAVs (https://en.wikipedia.org/wiki/Entity–attribute–value_model) and various discussion on how to handle them (https://dba.stackexchange.com/search?q=eav+is:question).

 

[udg]

Thanks @OliverA , I found your EAV-link very informative.
BTW, S1 + lookup table is still S1..eheh
I didn't mention the lookup table to shorten my question, but at least for documentation it's absolutely needed. For my real project I'll probabily end up with a few very specialized apps, each working on a small subset of possible "types", so a lookup table is a necessity.

I am temped to use JSON instead of converting a map to a sequence of bytes as the content of the generic vdata BLOB field. The reason is that JSON is a standard format so at any time I could grant access to that DB data to other code, eventually written in a different programming language.

ps: I'm not talking for myself..I'll stay as a loyal B4Xer..eheh

 

[OliverA]

I am temped to use JSON instead of converting a map to a sequence of bytes as the content of the generic vdata BLOB field

Have you ever thought about using PostgreSQL then? You can use JSONB and it can be indexed and queried.

See (https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/) and (http://coussej.github.io/2016/01/14/Replacing-EAV-with-JSONB-in-PostgreSQL/). On the flip side see (https://blog.2ndquadrant.com/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/).

 

[udg]

I read about it in the link you kindly provided on post#2 above. I wasn't aware of that!
I'm not sure they will let me install and use PostgreSQL instead of MySql, but I could ask..

Should they deny it, do you think that coding the BLOB data as JSON would have specific advantages on map-to bytes?

 

[OliverA]

If your JSON structure is less than 65K, you could use a VARCHAR as the column type (if you stick with MySQL). Any binary info would need to be encoded, but than you could "see" the JSON through regular queries which may help in troubleshooting any data storage/querying issue. For over 65K, you may go with cBLOB (BLOB for text). So for readability, JSON would have an advantage over map-to bytes.