How To Store Arbitrary And Transient Attributes With Your User Data

One thought is: "You're close" with "Adding Additional Columns To Your User Table". Instead of that, though, why not add a separate table, linked by the UserID as a foreign key. Then you keep your primary user data clean, but can throw any old column in. If you're using MSSQL, look into the SPARSE columns. I'm really liking them for places where I can't use a NoSQL solution, which would also be really good for this scenario.

Ooops. Re-reading, I see you have a similar option next. My eyes must've missed it!

You're still thinking horizontally when talking about making an "intro_video_viewing" table... what happens as you add more info about viewing the video? More columns. What happens when you need more info about some other aspect of your site? More tables with more columns.

Try this:

Users table (UserID, stuff you need)
UserDataTypes table (UserDataTypeID, label, type [like string, number, etc])
UserData table (UserID, UserDataTypeID, value)

Then just make a stored proc to pull back specific bits of data by UserDataTypes label and cast it to the right type. As you add new data types, you have an expanding capacity to track data and you never have to add more tables.

I don't know that specific user activities deserve their own DB table. With this design, you'd inevitably end up with "remarket_video_viewing" and "email_popup_viewing" tables as well. You'd find yourself with and endless list of "_viewing" tables. Might be better to refine those actions into a set of "user_event" and "user_event_type" tables, but maybe I'm misreading the intent.

Optionally, since you seem to be indicating the user would be logged in, so you'd know who they are, you could load their "extra" data into session storage, and just flush/commit any changes to these areas (session.userFlags, whatever) in onSessionEnd(), or something similar.

Then you could have separate routines for loading said "crazy, mismatched data" without interfering with your primary user save/fetch code. A listener/observer pattern might be an interesting way to go for login/logout.

I'm fine with the "Hash" approach. Fine enough that I've taken this approach a few times. If you view that JSON value as a discrete value, rather than as a set of data points, then it really doesn't violate relational normalization rules.

Yes- whichever method you use, there are compromises to be made. I've found storing JSON to be the least objectionable compromise. And not difficult to implement or change. In fact, this approach once saved me from a massive overrun on a project. Rather than redesigning the relational database structure and then the data layer of the application, I changed the JSON structure.

@Ben,

@Jon & @Aaron are definitely on the right path, also, don't be afraid to use a Left [Outer] Join. I would tend to call the table "user_preference" and create it like this.

user_preferences
--------------
* userId
* preference_type string or id - (this could include the IntroVideo, but give the flexibility in the setting)
* setting char(1) - (for the video, it could be multivalue, like remind-me, never-show, watched, etc., i would probably use a single character or id with a lookup table of values)
* create_date
* update_date

This way you check to see if the user has a preference and if not, set a default of "not set". The design is flexible and can be used in various ways. I would create an asynchronous updater for this table, as the user could update a preference numerous times, so you wouldn't want to hold up any other user action, just to save this.

Anyway, a table is the right model. How often and when to load it will become your next hurdle as a user's preferences could change mid-session. Also, since you're using AngularJS, you could setup an event loop that upon save, pulls the data back and stores it as it own Json object. This way, you aren't overloading the User object and if the Preference object is empty, the user get's defaults.

Hope this helps.

@Ben,

Exactly. It's a more generic design, and scales pretty well.

As a slight modification, you might add a "user_event_type" table, and store it's id on your "user_event" table.

user_event:
-----------
* userID (ex. 4)
* eventTypeID (ex. 2)
* occurredAt (ex 2014/01/15)

user_event_type:
-----------
* eventTypeID (ex. 2)
* eventTypeID (ex. hasSeenIntroVideo)

I don't know that you get any actual benefits in this, but it does feel "more normal".

Ooops, meant to have column 2 called "eventTypeName"...you get the point

What you guys are basically suggesting is the perfect use case for Redis. Redis is basically a super fast key-value store.

A lot of backends actually use Redis to store their persistent user sessions, so it's well suited for storing persistent user data of any kind.

There are a few adapters for CF and Redis.

@Ben + @Aaron,

Yeah, Aaron's example is pretty much what I'm saying, although his version is restricted to recording timings, where instead of having an something like "occurredAt" with a type of "date", I have a "value" column with whatever type is "any". This allows the table to hold any user data that is arbitrary... such as preferences for colors, time they viewed events, how they last rated the application, etc. If the idea of having a column with multiple variable types unnerves you (and it does some people) then you can extend this by having a table for each type you may want to store in your "value" field such as UserDataNumeric, UserDataDate, etc. that all have the same fields, just with different column types for "value". This does add a few more tables, but makes the "value" field less mutable. The downside is then that your CRUD code around these variables becomes a lot more complex, since you have to join and delete across many more tables and pick which one gets INSERTs based on the type of data.

Example from a past MSSQL project:

User
----
ID, bigint auto increment
... (other user stuff)
... (standard created/updated/deleted fields)

Preference
ID, bigint auto increment
Label, nvarchar
DataType, nvarchar (I actually abstracted this as another table)
... (standard created/updated/deleted fields)

UserPreferences
UserID, bigint, FK User.ID
PreferenceID, bigint, FK Preference.ID
Value, sql_variant (value of the user preference)
... (standard created/updated/deleted fields)

This setup meant that a simple query like:

SELECT
c.[Label] AS [Preference],
c.[DataType] as [Type],
b.[Value]
FROM User AS a
LEFT OUTER JOIN UserPreference AS b
INNER JOIN Preference AS c
ON c.ID = b.PreferenceID
/* And not deleted, etc */
ON b.UserID = a.ID
/* And not deleted, etc */
WHERE
a.ID = {ID}
/* And not deleted, etc */

Or something like that...

This is on a similar track to what @Jon and @Aaron are doing. One of the things that I've done in the past is very similar to their approaches, particularly because it is infinitely expandable.

What did I do differently? I created a view that performed a CROSS JOIN between the user and the data types, added a LEFT JOIN with the value table, then did a PIVOT so each data type is now a column for the user row. This makes it _look_ like the user table has these additional columns, but really it's implemented as a many-to-many in the database.

@Paul,

I've experimented with that in the past and found using PIVOT on the fly to be a dog in performance. Ended up using triggers to populate a denormalized version of the table to address performance and query complexity issues.

How do you approach those problem?

@Ben

I just think clogging up your MySQL database with a key-value store might not be the best solution. This data is not exactly critical (if you were previously willing to use a cookie), probably won't be used in a relational manor, and is infinitely expandable.

Redis couldn't be simpler. It sits in memory and backs itself up to disk. So it's forever persistent but SUPERFAST. There's a Java library called JEDIS which has been implimented in CF here: https://github.com/MWers/cfredis/

Consider that perhaps you want to store how much a video someone has watched and the resume when they come back to your page.

From ColdFusion (assuming you've got Redis library) you just do

var userData = {}
userData['introVideo'] = 82; //seconds watched
userData['anotherVariable'] = false;
redis.hmset('user:100', userData);

Now to read that data you just do

var userData = redis.hgetall('user:100');

This is WAY faster than a query because it pulls directly from memory and it's extremely convenient if you're using angular because you are one line away from returning that JSON to the front end. No fussing with cfquery.

Oh, and if you don't want to deal with an entire map you can also use

redis.hset('user:100', 'anotherVariable', 'somevalue');

that sets a specific value within a map. And

var anotherVariable = redis.hget('user:100','anotherVariable');

You should see the benchmarks on this stuff, it's insane. Even with 100,000 keys (users) filled with maps it will probably be less than a millisecond in most cases.

I think what's being described here is the entity attribute value (EAV) data model. You can read about it here:

http://en.wikipedia.org/wiki/Entity-attribute-value_model

You will find many opinions on EAV, some good, some bad. I've found it to be very useful, especially in the use case that you've described Ben.

@Zack,
I haven't had the opportunity to see how well it worked on large databases, unfortunately. I don't know how you would address that. If it's stored as a view, as I had suggested, it would probably be difficult to employ filters prior to doing the PIVOT. However, if you write a stored procedure, you could employ the filter before the PIVOT. Thoughts?

As stated above a Key/Value store like Redis might be a perfect fit. That would be my vote :)

IF all your values are boolean, you may consider having an integer (or larger type) column and use bit switches for the fields. Querying is supposed to be very fast, and when you run out of bits you can add another column.
Downside: not easily human readable.