StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POImplementing a find-or-insert for one-to-many tables
text
Body
copied!<p>I have 2 tables, <code>tracklist</code> and <code>track</code>, where <code>tracklist</code> has many <code>tracks</code>. At some points, I will receive user input which refers to a list of tracks, and I need to either create that tracklist, or return an existing tracklist (this is because tracklists are meant to be entirely transparent to users).</p> <p>My naive solution to this was to find all tracklists with <code>n</code> tracks, and join <code>track</code> against <code>tracklist</code> <code>n</code> times, checking each join against the user input data. For example, with 2 tracks:</p> <pre><code>SELECT tracklist.id FROM tracklist JOIN track t1 ON tracklist.id = t1.tracklist JOIN track_name tn1 ON t1.name = tn1.id JOIN track t2 ON tracklist.id = t2.tracklist JOIN track_name tn2 ON t2.name = tn2.id WHERE tracklist.track_count = '20' AND (t1.position = 1 AND tn1.name = 'Pancakes' AND t1.artist_credit = '42' AND t1.recording = 1) AND (t2.position = 2 AND tn2.name = 'Waffles' AND t2.artist_credit = '9001' AND t2.recording = 2) </code></pre> <p>However, this really doesn't scale well to large tracklists. My very rudimentary timing shows this can take >500ms for 10 track tracklists, and ~7s for tracklists with 100 tracks. While the latter is an edge case, whatever algorithm I use needs to be able to scale at least up to this.</p> <p>I'm stuck on other solutions however. The only other thing I can think of is to select all tracklists with <code>n</code> tracks, and all their tracks, and then do the comparison in application code. However, I'd really like to keep this on the database server if I can.</p> <hr> <p>Here is the schema I am working with:</p> <pre><code>CREATE TABLE track ( id SERIAL, recording INTEGER NOT NULL, -- references recording.id tracklist INTEGER NOT NULL, -- references tracklist.id position INTEGER NOT NULL, name INTEGER NOT NULL, -- references track_name.id artist_credit INTEGER NOT NULL, -- references artist_credit.id length INTEGER CHECK (length IS NULL OR length > 0), edits_pending INTEGER NOT NULL DEFAULT 0, last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE TABLE track_name ( id SERIAL, name VARCHAR NOT NULL ); CREATE TABLE tracklist ( id SERIAL, track_count INTEGER NOT NULL DEFAULT 0, last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); </code></pre> <p>Any suggestions?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload