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:

  1. Last date it was updated.
  2. Number of hits/downloads.
  3. 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:

  1. Date - Between 0-30 days 10 points, between 30-90 days 5 points, over 90 0 points.
  2. Hits - Between 0-5000 hits 0 points, between 5k-10k hits 5 points, over 10k 10 points.
  3. 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.

  • Please consider reading this advice. It seems to me that you're looking for the case expression. – mustaccio Oct 8 at 21:20
  • @mustaccio - I am using the case expression in my current view. I am trying to model how to do this via referencing criteria from other tables. Maybe its not that easy and the way I am currently doing it is right but it sure seems messy. So messy I didn't want to add the sql so not to confuse my actual goal. – LOSTinDB Oct 8 at 21:53
  • All three of these criteria are stored in that table (well tags are stored as comma seperated associates). This is bad. Only last update time must be stored - it is single value. Storing hits/downloads... imagine that some problem makes the value incorrect - you even have no criteria to detect the invalidity! Collect hits in separate table, pre-calculate daily grouped statistic when the next day starts. Tags - the table of tags must exist, and link table must be used. – Akina Oct 9 at 4:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.