Since you've only given us a vague description to work with, I can only
give you a vague answer in return.
For situations like what you've described above, I tend to use the
following schema:
CREATE TABLE widget ( widget_id SERIAL PRIMARY KEY,some_data TEXT
);
CREATE TABLE category (category_id SERIAL PRIMARY KEY,name TEXT
);
CREATE TABLE w_x_c (widget_id INTEGER NOT NULL REFERENCES widget,category_id INTEGER NOT NULL REFERENCES
category,PRIMARYKEY (category_id, widget_id)
);
-- primary key will implicitly create index good for mapping categories
-- to widgets, and here's an index to go the other way
CREATE INDEX w_x_c_rev_idx ON w_x_c (wigdet_id, category_id);
-- Which categories a widget belongs to (naturally you'd use a subselect
-- or whatever instead of 1)
SELECT * FROM category
NATURAL JOIN
SELECT * FROM w_x_c WHERE widget_id = 1;
-- Since you'll be doing this in the context of a web multi select,
-- You probably want a list of all the categories, one per row, with
-- a column (widget_id) that is either NULL or a number to tell you
-- if that row is selected or not.
SELECT * FROM category
NATURAL LEFT OUTER JOIN
SELECT widget_id FROM w_x_c WHERE widget_id = 1;
-- Of course that gives you a row for each category, which is mighty
-- handy if you're doing web work. But it sounds like you'd prefer
-- things in a different format:
CREATE FUNCTION fugly_concat_step (text, text) RETURNS text
AS 'SELECT ($1 || $2 || ''|'')::text;' LANGUAGE 'sql';
CREATE AGGREGATE fugly_concat ( BASETYPE = text, STYPE = text,SFUNC = fugly_concat_step,INITCOND = '|'
);
SELECT widget_id fugly_concat(category_id::text)
FROM w_x_c WHERE widget_id = 1
GROUP BY widget_id;
Now, when you want to add a new category, simply insert it into the
category table. Takes a little extra PHP coding, but you won't ever
have to update your code because you've added or changed categories.
--
Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
