I am working on a server-side application in which users will be able to give ratings and comments to different factors of a 'Restaurant'. What the webapp does is takes all the ratings and creates statistics out of it. The statistics are based upon day, week, month and year. As well as other things like gender ratio, age ratio, etc.

I am free to design an optimal DB model for this, and I have done some of it, but would like to ask if this is optimal, and if not, how can optimize it so its easier to maintain, understand, error-free.

The model I have right now for statistics seems a bit complicated to calculate, maintain. I am looking for some easier options.

Here is the information the visitor will give us :

  rating_id integer NOT NULL, // primary key
  age_range integer DEFAULT 0,
  comment_text character varying,
  friendly_stars numeric DEFAULT 0,
  gender character varying,
  other_stars numeric DEFAULT 0,
  overall_feedback boolean DEFAULT false,
  relative boolean DEFAULT false,
  quality_stars numeric DEFAULT 0,
  save_time timestamp without time zone

Here is the Statistics and monthly statistics I have. Monthly statistics will actually save daily computed statistics with month code, so it's easier to isolate months also from that was something I thought :

CREATE TABLE monthly_statistic
  mstat_id integer NOT NULL, // Primary key
  daily_value timestamp without time zone,
  female_ratio character varying,
  friendly_quality numeric DEFAULT 0,
  high_age_ratio character varying,
  less_age_ratio character varying,
  man_friendly numeric DEFAULT 0,
  man_ratio character varying,
  mid_age_ratio character varying,
  other_quality numeric DEFAULT 0,
  service_quality numeric DEFAULT 0,
  woman_friendly numeric DEFAULT 0,
  statistics_id integer NOT NULL, // assocication with Statistics

And finally, here is Statistics :

CREATE TABLE statistics
  statistics_id integer NOT NULL,
  less_age_month character varying, // Less age means 18-24
  less_age_week character varying,
  less_age_year character varying,
  mid_age_month character varying, // Mid_age means 24-60
  mid_age_year character varying,
  mid_age_week character varying,

 more_age_month character varying, // More age means 60+
  more_age_week character varying,
  moreageyear character varying,
  month_friendly numeric DEFAULT 0,
  month_man character varying,
  month_other numeric DEFAULT 0,
  month_quality numeric DEFAULT 0,
  month_woman character varying,

  week_friendly numeric DEFAULT 0,
  week_man character varying,
  week_other numeric DEFAULT 0,
  week_quality numeric DEFAULT 0,
  week_woman character varying,
  year_friendly numeric DEFAULT 0,
  year_man character varying,
  year_other numeric DEFAULT 0,
  year_quality numeric DEFAULT 0,
  year_woman character varying,
  restaurant_branch_id integer NOT NULL, // Association with resto branch

So as you can see I have created values for week, month and year. But if the user wishes to see last week or this weeks statistics, then I need something more fine-grained, that's why I created monthly-statistics, with which I can save information based on per day.

I have to also show information like, within 1 week or 1 month or 1 year, how many of them were males or females, how many were with age 18-24, etc. I hope you guys get the idea. Kindly let me know if this data-model sounds suitable or if you guys have any idea for a better one.

Please note, that statistics are calculated only in night, not real-time and I am using PostgreSQL.. Have a good day.

  • Sounds like you are emulating materialised views - read up on these and see do they do what you want. Also, check out any open-source rating software out there - pick and mix from their code/schemas as suits. p.s. I'm fairly sure that many 24 year olds would object to being classified as middle-aged! :-) – Vérace Oct 27 '15 at 14:18

Save all of the raw ratings (i.e. individual user inputs) with the date on which the rating was recorded.

Then you can calculate all of your weekly, monthly and yearly statistics based on the raw data each night. These rolled up statistics should also be dated (with a date range). In this way you can also keep multiple weeks, months and years so that you can look at trends if you like.

This is often called a data warehouse. It is a common way of pre-calculating rolled-up statistics for quick and easy reporting.

When you get past the longest of your roll-ups (e.g. 1 year) you can start to throw away your raw transactional data if you're worried about how much space it takes up.

  • I am saving the raw ratings. But for the weekly, monthly and yearly, I was looking for something simpler. What you say sounds simpler I must agree, but how to save all that weekly, monthly and yearly stats is my problem. – We are Borg Oct 27 '15 at 12:37

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.