On Wed, Apr 12, 2017 at 3:56 PM, Gavin Wahl <gw...@fusionbox.com> wrote:
> > 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.
