mardi 24 avril 2018

Use TOXI Solution in DataBase with Json data 101

we want to project a new database schema for our Society's application.

The program is developed in c# nancy serverside and react-redux-graphql on clientside. Our Society often must iplement repentine changing for treat new business data. So we want to realise a solid corefor the fundamental and no subject to decadence data eg: Article (Code, description, Qty, Value, Price, categoryId).

But often we need to add particular category to an article, or special implementation only for a limitated period of time. We are thinking to implement a TOXI like solution for treat those situations. But in TOXI pattern iplementation we wan to add a third table for define each tag data type and definition. Here is a simple explanatory image:

enter image description here

In the Metadata whe have two columns with JSON data: DataType and DefinedValue

  • DataType define How the program (eventually a func in db) must cast the varchar data in articoli_meta.value

  • DefinedValue is not null define if the type must have a serie of predefined value eg: High, Medium, Low etc...

Those two column are varchar and contain JSON with a predefined standard, a defined standard from our programming team (ev. an sql func for validate those two Columns)

I Understand thath this kind of approach is not a 'pure' relational approach but we must consider that we often pass data to the client in json format so the DefinedValue column can easily quered as string and passed to interface as data for a dropdown list.

Any ideas, experience or design tips are appreciated

Aucun commentaire:

Enregistrer un commentaire