Automation Process for Website PHP PostgreSQL

Question

Hello,

Currently, I'm working with a website. This website is written using PHP with PostgreSQL as the back-end. For the server I use Apache (XAMPP). My website has a membership feature consist of free membership and premium membership. Premium membership valid for 1 year after the registration, after that the user membership will be revert back to free membership. To be a premium membership again, the user should pay the membership fee and the account will be upgraded again for the next 1 year (imagine Rapidshare membership!). How to create a automation process to check and demote an expired membership? I think it should be a background process working on the back-end (postgresql), but I'm not really sure. Any idea or solution?

Big thanks :)

Answer 1

Use a table schema like this one:

CREATE TABLE all_accounts (
    id INTEGER NOT NULL PRIMARY KEY,
    premium_expires DATE NOT NULL DEFAULT current_date,
    other_attrs,
    ...
);

Then create a view which you use for queries:

CREATE VIEW accounts AS
SELECT
   account.*, current_date < account.premium_expires AS is_premium_membership
FROM
   account
WHERE 
   id = ?

Now a select to accounts will yield an is_premium_membership attribute which is t when the premium membership is valid. No need for background jobs.

Answer 2

Why don't you check every time when user logs in?