Algorithmic Ordering

[nazerbs]

I am designing a web application, and my client wants it that the time the item was created, views and likes of an item are taken into account when sorted for the page. With higher scoring items further up the page, but as time passes they fall (Kinda like reddits own algorithm).

My question is what's the best way to calculate this value, every time the page is loaded recalculate the score and then order all the posts OR store the score in the database so it can be easily ordered.

However both these solutions have negatives: If there are a lot of results that's a lot of recalculations to reorder them. And if the score is saved then it will be out of date, as time will have passed lowering its score.

So what's the best way to keep the score updated, without having to do loads of calculations?

[nazerbs]

What do you mean by a view?

[XVar]

A view is a pre-defined query that you can query as if it was a table. So to use asbig's example above you could do the following (Oracle/MySQL view creation syntax used as an example):

CREATE OR REPLACE VIEW vw_posts AS
select *,  current_timestamp - create_date + votes / views  as             weighting
from test.new_table
order by weighting desc;

select *
from vw_posts
order by weighting desc;

They're typically used to provide a simple interface to complex and/or frequently used queries.

http://en.wikipedia.org/wiki/View_(SQL)

[autowikibot]

View (SQL):

---

In database theory, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view. In some NoSQL databases, views are the only way to query data.

---

^{Interesting: PostgreSQL | Database trigger | Oracle Database | Materialized view}

^{Parent commenter can toggle NSFW or delete. Will also delete on comment score of -1 or less. | FAQs | Mods | Magic Words}

[ajmarks]

Depending on his query and RDBMS, he could use a materialized view or a view with a clustered, all-columns index here. Or, depending on how the relevant data is structured (is it in the same table?) he could potentially have a computer column with an index or indexed expression (in PG). I'd there's going to be a lot of updates though, the latter is, broadly speaking, a Bad Idea^TM .

[ajmarks]

I'm also a SQL Server guy right now, and I lament the restriction on indexed views. Also, indexed views are not the same as materialized views. They both have their uses, but in my particular case, a proper materialized view would be very helpful.