Custom fields, pivots, EAV and sparse tables

• kangarolf

  Hall of Fame

  Points: 3432

  More actions

  February 13, 2019 at 3:32 am

  #382717

  Hi all,
  We have a multi tenant SAS app that allows people to add custom fields in relation to a couple of entities.

  At present I store the custom field definitions/meta data in one table and the data in another long, thin table keyed to the entity ID and the custom field ID.

  Our users want more control/reporting flexibility and dynamically pivoting the data each time is very burdensome and results in some very hard to read and optimise queries.

  I am considering either;

  1 - using a wide sparse data table and limiting the numbers of custom fields available (no-one currently uses more than 10 so I was planning on providing 30 each of text, number, bit and date fields = 120 columns ish). Storing the custom field meta data as we do now, along with a type and reference to which column is being used, and the using the front end to dynamically generate the col headings and retrieve the direct data without having to pivot.
  This means a lot of work across the app altering queries and UI elements

  2 - Pre-pivoting the data into tables (one for each tenant) at set intervals and updating as entity data is updated and restructuring (ALTER TABLE) as the customers change/edit custom fields. This means less and easier work across the app but where applicable I can do regular joins to extract the data without any dynamic pivoting. Consistency and integrity arr concerns here.

  Any comments or suggestions much appreciated as this is new territory for me.
  Thank you.