Selecting a daily puzzle record - which type of column to add?

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Selecting a daily puzzle record - which type of column to add?
Date: 2017-09-17 20:13:10
Message-ID: CAADeyWhxdCO4GKHnmS9FXFCO745eQ+6_nDj2jQVF0xWLJZjrTA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Good evening,

In a word game I store all player moves in the table:

CREATE TYPE words_action AS ENUM (
'play',
'skip',
'swap',
'resign',
'ban',
'expire'
);

CREATE TABLE words_moves (
mid BIGSERIAL PRIMARY KEY,
action words_action NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb,
score integer CHECK(score >= 0)
);

I could run a cron job on all moves played each day and select the
"spectacular" ones by it, i.e. when a very big score has been achieved in
the move or all 7 tiles have been played...

Then I (as admin of the game) would manually review the daily mails sent by
that cronjob and select the few I have found interesting - for later
publishing them as "daily puzzle" in my day.

However I don't want to do the reviewing every day as that would be
tedious, but more like once per week and then select several such moves at
once (for the future dates).

My question (and thanks for reading my mail sofar) is: which column would
you add to the words_moves table for such a purpose?

If I add a boolean (is a daily puzzle move: true or false) - then it is
difficult to keep the order of the daily puzzles, I think.

If I add a timestamptz, then to which date to set it, when I do my manual
review once a week?

I need to add a useful column, so that it would be easy to me to create a
web script which would display today's and all past "daily puzzle" records
- and wouldn't change the already published puzzles...

If you have a good idea here, please share with me. If not, sorry for the
maybe offtopic question.

Thanks
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Nikander 2017-09-17 20:33:06 advisory locks namespace?
Previous Message Tom Lane 2017-09-17 19:30:49 Re: [GENERAL] Remove useless joins (VARCHAR vs TEXT)