I am taking a first stab in designing a system that gives ratings to our assets based on various criteria.
So to simplify what I am doing - it will be this times 10...
I have an asset tables (docs/video/webpage) and we will give it a rating based on:
- Last date it was updated.
- Number of hits/downloads.
- The number of tags associated with it.
All three of these criteria are stored in that table (well tags are stored as comma seperated associates).
We want to have tiers of weighting based on the values above.
An example is:
- Date - Between 0-30 days 10 points, between 30-90 days 5 points, over 90 0 points.
- Hits - Between 0-5000 hits 0 points, between 5k-10k hits 5 points, over 10k 10 points.
- Tags - Between 0-5 tags 0 points, between 6-10 tags 5 points, over 10 tags is 10 points.
Then we add up the points for each asset and give it a number.
I have already started doing this just using a simple mysql view and a TON of if/then statements. It works but it is really sloppy and it does not anyone else to edit the criteria or values.
Since I have never designed anything with a dynamic amount of criteria - so for instance changing the number of hits for the various tiers or changing the points for each tier or even changing the number of tiers - I figured I would ask what are the best practices in setting up tables and relationships to do this most efficiently.