3

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)

hellion
  • 4,602
  • 6
  • 38
  • 77
  • hstore seems like the way to go. But, if one user creates a category then I would like that category to be available for all users to use. hstore seems like it will be user specific and maybe not very organized – hellion Jun 09 '13 at 22:58
  • The best option I can come up with is to create a column for every datatype I can think of that a user might need to log...and then have them submit a request for me to add more data columns if needed. – hellion Jun 09 '13 at 23:00
  • Another option I just though of is to create a set number of custom columns in the database, say...custom_one, custom_two, etc (maybe 10 of them). Then, create a Usercustom model with two fields...custom_col_ref:string and custom_col_label:string. custom_col_ref would store the actual column name where the data is stored, and custom_col_label would store the label to be used in the form. Then, use an if statement to show/hide that field in the form..."if Usercustom.find_by_custom_col_ref("custom_one").exists?"... show a field for that column in the form. – hellion Jun 09 '13 at 23:15

2 Answers2

4

The usual approaches to this are:

  • EAV
  • hstore
  • XML
  • JSON

See:

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.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 2
    "Adding columns with dynamic DDL sounds reasonable at first" and then you have to convince the ORM to play nice with this sort of thing. You'll probably have a better time bobbing for apples in a bucket of fish hooks. – mu is too short Jun 10 '13 at 01:59
  • 1
    @muistooshort Ah, yes, ORMs. Fun times. Delightful analogy. – Craig Ringer Jun 10 '13 at 02:13
  • Thanks, Craig. So, if I know 90% of the fields users will need would it be proper to add those columns to the database...even though for some/many users many of those columns will always be empty? Im using postgres, so I could use hstore...but, still figuring out how to do that within my vision. – hellion Jun 10 '13 at 17:03
  • @hellion If you're not talking hundreds of fields, and you're not planning on adding and removing them dynamically, then it's probably OK. I'm pretty sure you'll regret it as users start asking for more and more though; I'd make it something dynamic from the start. At the very least I'd use a side-table I joined against rather than adding the cols to the main table, so I could change my approach later without so much pain. – Craig Ringer Jun 11 '13 at 01:12
0

I didnt end up using hstore because I didnt think it was the best solution. It might be...I just dont want to invest the time and fumble around figuring it out. What I ended up doing is altering my existing Preferences model and creating a few new models. In the Preferences model I have a string column where I store the name of a referenced model and that model items id...then constantize the string when needed.

So, if a user wants to include fruit in their preferences, they can click "add more fruit", and if the specific fruit they want isnt listed they can add it. When they save their preference the selected fruit id will be stored as an integer and via a hidden field the referenced model (in this case "Fruit") will be stored as a string.

Then, in the users preferences index page I have a section titled Fruit (if the user has any Fruit preferences). The query simply searches the Preference table for user matching current_user, modelref matching "Fruit", and find the fruit record using the un-associated stored id.

I may not have explained that very well...but, maybe it will help someone else looking to do something similar.

hellion
  • 4,602
  • 6
  • 38
  • 77