Re: [GENERAL] Selecting a daily puzzle record - which type of columnto add?
От | Berend Tober |
---|---|
Тема | Re: [GENERAL] Selecting a daily puzzle record - which type of columnto add? |
Дата | |
Msg-id | a41107c4-fcc5-0680-fbd4-1e1c17f3ad3f@computer.org обсуждение исходный текст |
Ответ на | [GENERAL] Selecting a daily puzzle record - which type of column to add? (Alexander Farber <alexander.farber@gmail.com>) |
Ответы |
Re: [GENERAL] Selecting a daily puzzle record - which type of columnto add?
(Melvin Davidson <melvin6925@gmail.com>)
|
Список | pgsql-general |
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_datetimestamptz /*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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
В списке pgsql-general по дате отправления:
Следующее
От: Melvin DavidsonДата:
Сообщение: Re: [GENERAL] Selecting a daily puzzle record - which type of columnto add?