I have a simple database table, lets call it
tbl_main with a few columns and relations to other tables via foreign keys.
tbl_main is the most frequently written to table, as in it receives new rows (not so much updating existing rows).
The application should be able to support various additional data related to this table that adds valuable information to the entries in
Yet it is not known ahead of time what kind of information this will be. E.g. it can simply be a value, an array or an object/document or a combination of those types.
(When) Should I
- create several new JSON columns for each new related kind of data or
- create one JSON column for every kind of related data or
- create a new table referencing the main table via foreign key (possibly via another junction table) for every new related data or
- create one separate meta table
tbl_main_metathat has a foreign key relationship and one binary column for the additional data?
I would like to keep the database architecture and the application architecture extensible for future data that is related to this
tbl_main, yet it should be easy/efficient in the application to query for this additional data and to process it for multiple queried rows of
- additional data is only one integer value (may be null) and should be summed up.
- additional data is a list of integer values with different units, each unit's values should be summed up for all queried rows of
- additional data are multiple documents that should be both summed up for unit's values but also returned if there are string entries
The answer could also be that I must evaluate this for every new related data that I want to support, but then how should a general design look like, because I assume the meta_table approach is bad practice/inefficient?