Forum OpenACS Development: Re: AMS package ams_options
As you know, the ams_option_types table, lists the valid options per attribute.
In ams_attribute_values (the master skinny table for ams) there is a one to one to one mapping of attribute_id, value_id and object_id for the sake of speed, i tried one to many and it was a lot slower at data retrieval then a simple one to one (which is most often the case). This table only has indexed integers and is reasonably fast.
This is great of text input, addresses, telecom numbers, etc. But for multi value widgets such as checkboxes or multiselect it wouldn't work since its may options to one value_id. So, there is the ams_option_ids table, this is a index of possible answers to ams_option_types. Whenever somebody answers a question, lets say what colors you like (the options are found in the ams_option_types table):
with A and B they get the same ams_option_ids table, value_id. This is one of the ways in which this skinny storage is kept relatively fast - since we only store unique values, with one value_id (instead of creating a new value_id for the same response).
Anyways, the ams_options table is a mapping of value_id to option_id, so it stores that value 1234 has option type A and B. And value 1234 can have option B by itself, etc.
The naming of these tables is a bit wired. The reason its named this way is because many of the other storage mechanisms for attributes such as text, number, dates, postal_address, telecom_number are always one value_id to one answer, and their tables are named ams_texts, ams_times, ams_numbers respectively.
That said, the ams tcl api is complete enough to run contacts (the most complex) and it does this all behind the scenes.