[GENERAL] Efficient data structures and UI for product matrix

Hi!

We wish to provide our users with a simple-to-use web-based processor-selection tool, where a user could select a couple of attribute values and be presented with a list of matching processors. The basis of the required data would be provided by our editors as Excel documents of the following structure:

attribute_1 attribute_2 ...
processor_a some_value some_value ...
processor_b some_value some_value
...

This data would be normalized to the following structure on import:

CREATE TABLE processors
(
id serial NOT NULL,
processor_name text NOT NULL,
CONSTRAINT "processors_pkey" PRIMARY KEY (id)
)WITHOUT OIDS;

CREATE TABLE attributes
(
id serial NOT NULL,
attribute_name text NOT NULL,
CONSTRAINT "attributes_pkey" PRIMARY KEY (id)
)WITHOUT OIDS;

CREATE TABLE processor_attributes
(
processor_id integer NOT NULL,
attribute_id integer NOT NULL,
value_id integer NOT NULL,
CONSTRAINT "pk_processor_attributes" PRIMARY KEY (processor_id, attribute_id, value_id),
CONSTRAINT "fk_processor_id" FOREIGN KEY (processor_id) REFERENCES processors(id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT "fk_attribute_id" FOREIGN KEY (attribute_id) REFERENCES attributes(id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT "fk_value_id" FOREIGN KEY (value_id) REFERENCES attribute_values(id)
)WITHOUT OIDS;

CREATE TABLE attribute_values
(
id serial NOT NULL,
value text,
attribute_id integer NOT NULL,
CONSTRAINT "attribute_values_pkey" PRIMARY KEY (id),
CONSTRAINT "fk_attribute_id" FOREIGN KEY (attribute_id) REFERENCES attributes(id) ON UPDATE CASCADE ON DELETE CASCADE
)WITHOUT OIDS;

The (web-based) UI should provide a dropdown field for each attribute (none selected per default) and a pageable table with the matching results underneath. The user should be kept from having to find out that there's no match for a selected combination of attribute-values, so after each selected dropdown, the as yet unselected dropdown-lists must be filtered to show only the still available attribute values - we intend to use some AJAX functions here. It'd be nice if the UI could be made fully dynamic, that's to say that it should reflect any changes to the number and names of attributes or their available values without any change to the application's code; the latter is in fact a must have, whereas the number and names of attributes would not change quite as frequently, so moderate changes to the code would be alright.

Now, has anyone done anything similar recently and could provide some insight? I'd be particularly interested in any solutions involving some sort of de-normalization, views, procedures and suchlike to speed up performance of the drop-down-update process, especially as the number of attributes and the number of legal values for each attribute increases. Does anybody know of some sort of example application for this type of problem where we could find to inspiration?

Kind regards

Markus

Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jürg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general