Efficient data structures and UI for product matrix - Mailing list pgsql-general

Hi!

We wish to provide our users with a simple-to-use web-based processor-selection tool, where a user could select a
coupleof attribute values and be presented with a list of matching processors. The basis of the required data would be
providedby 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
withthe matching results underneath. The user should be kept from having to find out that there's no match for a
selectedcombination of attribute-values, so after each selected dropdown, the as yet unselected dropdown-lists must be
filteredto show only the still available attribute values - we intend to use some AJAX functions here. It'd be nice if
theUI could be made fully dynamic, that's to say that it should reflect any changes to the number and names of
attributesor their available values without any change to the application's code; the latter is in fact a must have,
whereasthe number and names of attributes would not change quite as frequently, so moderate changes to the code would
bealright. 

Now, has anyone done anything similar recently and could provide some insight? I'd be particularly interested in any
solutionsinvolving some sort of de-normalization, views, procedures and suchlike to speed up performance of the
drop-down-updateprocess, 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