1

I have a simple database table, lets call it tbl_main with a few columns and relations to other tables via foreign keys.

The 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 tbl_main.

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_meta that 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 tbl_main.

Example:

  • 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 tbl_main
  • 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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.