-- The songs, albums and artists a person marked as favourites. There is no foreign key on the item (it is in one of -- three tables): a favourite whose item is gone is ignored when read. CREATE TABLE favorites ( user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, kind TEXT NOT NULL CHECK (kind IN ('artist', 'album', 'track')), item_id INTEGER NOT NULL, created_at INTEGER NOT NULL, -- unix seconds PRIMARY KEY (user_id, kind, item_id) ); CREATE INDEX idx_favorites_time ON favorites(user_id, kind, created_at DESC);