I have a postgresql table that contains widgets and their various attributes. Each widget belongs to a group of users, but each user in the group may want to archive/hide any given widget from their view. There can be hundreds of these widgets and some users only care about some subset of them, but that becomes a per-user preference.
I'm at a loss as to how to efficiently implement this. I could include an "inactive" column on the widget table with a list of users that have hidden each row, but that seems horribly inefficient. Flipping it around so I store hidden items in a user profile doesn't seem much better as that list could get arbitrarily large over time. Are there any best practices for this sort of problem?
You are looking for a junction table. Here is an outline:
create table widgets (
widgetId serial primary key,
. . .
);
create table users (
userId serial primary key,
. . .
);
create table userWidgets (
userWidgetId serial primary key,
userId int not null references users(userId),
widgetId int not null references widgets(widgetId),
isVisible boolean
);