From: Richard Huxton <dev@archonet.com>
To: pgsql-sql@postgresql.org
Subject: Eliminating duplicate lists
Date: Tue, 26 Aug 2003 18:30:31 +0100
Message-ID: <200308261830.31192.dev@archonet.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com
Subject: Re: Eliminating duplicate lists
In-Reply-To: <200308261830.31192.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox