From: Richard Huxton <dev@archonet.com>
To: pgsql-sql@postgresql.org
Subject: Eliminating duplicate lists
Date: Tue, 26 Aug 2003 18:30:31 +0100
User-Agent: KMail/1.5
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200308261830.31192.dev@archonet.com>

TIA all...

Four tables:
  content: content_id, content_name, ...
  content_features: content_id, feature_id
  device_features: device_id, feature_id
  device: device_id, device_name, ...

One item of content can run on many devices, providing all the required 
features in "content_features" correspond to those in "device_features". One 
device can run many content items.

Now - I have functions to return compatibility for a single piece of content, 
but I also need to build static compatibility lists, something of the form:

content_compat: content_id, compat_list_id
compat_list_info: compat_list_id, compat_list_name, ...
compat_list: compat_list_id, device_id

What I don't want are any duplicate lists. By which, I mean if list "A" 
contains devices 1,2,3 then there should be no list "B" which contains 1,2,3 
(and no others).

Of course, new content items and devices are added regularly and shouldn't 
require rebuilding the entire table (just to make life interesting).

Solution 1
Introduce a "compat_uniq_code" into table "compat_list_info".
This would be composed of all the features supported by this list, built via 
plpgsql, something of the form "content-type:7:8:9" for features 7,8,9. I can 
then use this as a key and checking for duplicates is easy. Note that the 
feature ids will have to be sorted.

Solution 2
Have a temporary table - build each list there and then join against 
compat_list and make sure that for any given compat_list_id there are either:
 1. items in temp_compat_list but not in compat_list
 2. items in compat_list but not in compat_list
You could avoid the temporary table with a temporary compat_list_id and a 
self-join on the compat_list table.

Solution 1 is a somewhat ugly procedural hack, and 2 isn't going to be a 
simple query and is probably going to be slow.

Anyone got any better ideas?

-- 
  Richard Huxton
  Archonet Ltd