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_list2.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
