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
