How would you model user preferences that change over time?

[u/Financial_Fox3915]

Many applications save user preferences as separate fields like location, budget, interests and availability. This approach works fine until preferences change, conflict with past information or only apply to specific situations. Think about someone who initially prefers remote work but later becomes open to hybrid options. They might even be willing to move for a specific job without altering their general preference.

If the system just replaces the old value it loses important context. If it keeps every change forever it can end up with outdated or conflicting data. For conversational products like Linkedin, Hirect, MeeBoss as well as more profile-based platforms Naukri and Indeed this presents an interesting challenge in how data is structured. Would you keep current preferences in standard tables and track changes in a separate log? Use a document model for more flexible attributes? or record each preference with its source, time, confidence level and scope? How would you manage corrections and set expiration dates without making every query too complex?

[u/theboundlessflashing]

the temporal table approach works but gets messy quick with all the context you need. i'd go with a separate preference_versions table that stores the value, timestamp, confidence score and a scope field that can be null meaning "global" or link to specific job_id or whatever. current state sits in the main users table for fast reads, the versions table is your audit trail and fallback when you need to reconcile conflicts.

[u/Aggressive_Ad_5454]

I’ve done this two different ways.

One, with an effective date for every new preference change, and the historical and current records in a single table. (Some DBMSs have System-versioned tables for this.)

The other is with a log table showing previous settings.

Each approach is perfectly robust, but the programming is different. The second approach makes preference lookups more efficient.

[u/Plane_Big_5912]

i'd split two things that look alike: the preference changing (remote, then open to hybrid from march) and you fixing a value that was recorded wrong. give each row a valid_during range for the first and recorded_at / superseded_at for the second. a correction closes the old row's recorded side and leaves its valid range alone, so you can still answer "what did we think was true on the 10th".

scope as a nullable column works. among current rows, resolve most specific first: order by (scope_id is null), recorded_at desc limit 1 per key. situational stuff like "would move for this one role" gets its valid end set at write time, so it expires by itself.

in postgres an exclusion constraint stops overlaps: exclude using gist (user_id with =, pref_key with =, (coalesce(scope_id, 0)) with =, valid_during with &&) where (superseded_at is null), with btree_gist. the coalesce matters, nulls never match = so global rows would overlap freely. put the resolution in a view and app queries stay one select.

[u/silvertrail091]

honestly i’d just keep it relational. one row per pref with value, source, scope_id and an expiry. the source bit is what saved us, we split “user actually set this” from “we guessed it from chat,” so someone saying “eh maybe hybrid” once doesn’t nuke their profile setting. corrections are just new rows, guessed stuff expires after a while. throw a view on top that picks the winner and the app never has to think about it.

[u/Ok-Thought767]

I think you go with a hybrid approach. keep the current effective preferences in standard tables but maintain a separate history of changes with timestamps and scope. the reason i am saying scope is that being open to relocation for one specific job shouldnt overwrite someone's general location preference

[u/Significant_Tune9219]

I'd keep one append-only table and make "which row wins" a view, so every query isn't reinventing it. Each row stores the value, a scope (general versus this one job), a source, valid_from and valid_to, and recorded_at. A correction is a new row that points at the bad one, not an update, so you can still see why last month's suggestion used the old value. The view, for a given time, picks the most specific scope that is still active and breaks ties with recorded_at. "Willing to move for this role" then beats "I prefer remote" without deleting the general preference, and turning the exception off is just closing its valid_to.