> Given this limited example I'd probably choose to model notifications as an
> array on the user table. Then just "UPDATE user SET notifications =
> array['a','b']::text WHERE user_id = 1;
I'm hesitant to ditch the first normal form just to get around this. Anyway,
there's actually extra data in the table that makes it hard to use an array:
CREATE TABLE notifications (
user_id INT,
type CHAR(1),
threshold INT,
some_options BOOLEAN,
PRIMARY KEY (user_id, type)
);
A custom composite type would solve that part of the problem.
You're going to have to pick you poison here. No serializable, no locking, and no atomic data type. I don't have any other reasonable ideas that aren't any worse than any one of those three. You would need to introduce some kind of "notification set id" and make (user_id, active_notification_set_id) the linking multi-column key.
Or wait and see if anyone more clever than I has some ideas.
David J.
