I have a small/fixed number of items that I’d like to rate across a large/expanding number of categories and display a weighted average of those ratings in a separate view. I have this mostly working, but the number of kludges and hidden fields is expanding so rapidly that I suspect my basic architecture is wrong. Is there a better way than this?
This is how I have things set up:
- each item has its own table
- categories are records with ratings (as single select of
-3 ... +3)
- to use the same set of categories for another item, I duplicate the table and rename it
- weights are a separate table of decimal values
0...1 (keyed by category name), I link the categories to these by adding a hidden formula column ={Name} then coercing it into a link to the weights table (this already feels pretty kludge-y)
- bring weights back into the item table with a lookup field, calculate weighted value of the rating as another field
- in a Summary table, link all the category records (manually, ugh) to a summary record, then use a rollup with AVERAGE
- now I’m stuck, since a field can only link to one table and my items each have their own
Conceptually I think the big issue is that I want to represent a comprehensive “all to all” relationship. I can solve some of my issues by putting all the item-category pairings into a single table and creating views for each item, but manually creating every relationship (and updating them as I add more categories) is quite daunting.
Is there a better way?
