The background is a site which features holiday properties, and we are at the point where we would like to add a ratings system allowing people to vote.
There is currently a back end database built for this.
In which case I’d be really grateful for any pointers as to how difficult / complicated it might get.
Off the top of my head, I’m thinking along the lines of…
We already have a table - Lodges. So in theory this just needs two new fields, ‘Average Score’ and ‘Total Score’.
And we need a new table ‘Lodge_Scores’, with a one (Lodge) to many (Scores) relationship.
Which seems straightforward enough.
The bit I’d be less sure about would be how to calculate the average score and total score, and get those values into the ‘Average Score’ and ‘Total Score’ fields.
Hopefully that makes sense, and might not actually be too complicated.
Thanks.
Hi john,
first I’ll warn you that I’m not a db person.
second we don’t know which db you use.
But so here I go:
is total_score the total number of scores? so that one can just serialise/increment? That one sounds easy. Unless people can delete a review.
For average score… I know in Postgres we’ve got TRIGGER functions you can write either in PL/PGSQL or some other PL/* language like PL/Python or PL/Perl…
so like whenever the Lodges table gets an INSERT/DELETE/UPDATE of the Average_Score column, this function would run automatically in the db and only change table stuff.
If your backend does other stuff with this though, then I guess you’d need to read the whole Average_Scores related table and Do Stuff with your backend language.
One disadvantage of using the PL/* stuff is, it’s completely in its own environment, should have no access to your filesystem (a Good Thing prolly) or libraries/modules you might already have for your language, and debugging is apparently a pain. So only use if it’s it’s a simple one-off kind of function. Also your scores would have to be DOUBLE PRECISION and maybe check that you don’t /0 if all scores were deleted at once or something…
http://www.postgresql.org/docs/current/static/functions-aggregate.html scroll to the bottom and it seems there are built-in averaging functions.
I’m pretty sure Oracle db’s would have this too, but I don’t know if MySQL does. Probably.
BTW it’s possible, maybe likely, that these scores will get text, right? reviews?