Dynamic table columns based on user preferences

Question

Scenario

Lets say a user is a salesman. The User model has many log_entries used as a daily log for sales data. The user also has preferences that allow them to select what fields are visible in their log_entry form. So, if they select pineapples, bananas, and grapes...those are the fields that will be in the form. If these choices change, the data will not be lost...just not visible in the form. Now, lets say the user just picked up cranberry sales account, but cranberries are not a choosable attribute in their user preferences form. They need to be able to create that "category". Now, pineapple season is over so they log in to there preferences and uncheck the pineapple box and check the strawberries box. Now, when they go to enter data in their log there will be a data field for strawberries, but not pineapples. The pineapple data is not gone...its just not being shown.

This scenario creates a couple challenges. One being that as a developer I don't know the database column names ahead of time because the users can create new 'categories' on the fly (I could require that users contact me to add more as needed, but...). And, when a user creates a new category...the log_entries table wont have a column to represent that new data.

How can I manage dynamic db columns? Can postgres hstore handle this?

One row is an entry belonging_to a user based on date. Each column holds data specific to an attribute (such as...one column could be titled 'strawberries' and it would hold how many units were sold that day)

Answer 1

The usual approaches to this are:

• EAV
• hstore
• XML
• JSON

See:

• Database design - should I use 30 columns or 1 column with all data in form of JSON/XML?
• https://dba.stackexchange.com/q/27057/7788

The whole "make columns available to other users" thing just requires you to keep a "custom keys" table that you add to whenever a user defines a previously unused key.

Adding columns with dynamic DDL sounds reasonable at first, but there are limits to how many columns you can store and how "wide" a row can be. Performance of scanning the table gets worse as you add more columns, though "sparse" columns that are mostly null are relatively cheap. An exclusive lock is required to add a column, something that can take time to get on a busy system, though adding the column its self is very quick if it isn't defined as NOT NULL DEFAULT .... It'll work fairly well at first, but I suspect you will regret doing it later.