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

On Mon, Sep 18, 2017 at 7:59 AM, Berend Tober <btober(at)computer(dot)org> wrote:

> Alexander Farber wrote:
>
>> 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.
>>
>
> I like the idea of a new column in words_games that allows nulls and to be
> filled in subsequently with the review date, but here's another idea to
> consider:
>
> If you have another place to store control information, you could store
> the mid value of the last-reviewed words_moves table row. That serial
> column also keeps track of the order, btw.
>
> Or maybe you define another table capturing more detail, if you need it,
> such as
>
> CREATE TABLE spectacular_moves (
> mid BIGINTEGER REFERENCES words_games,
> review_date timestamptz NOT NULL,
> publication_date timestamptz /*NULL allowed ... date to be filled
> in subsequently */,
> );
>
> Or those last two columns could both be appended to the word_games table,
> again, allowing NULL, but then filled in as the events occur.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

I don't have any specific suggestion for an additional column, other than
Berend's idea. However, I strongly advise against the use
of ENUM's. They can create a major problem in the event one needs to be
removed. It is a lot easier and simpler to use a
Foreign Key table instead.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.