On Mon, Sep 18, 2017 at 7:59 AM, Berend Tober <bto...@computer.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@postgresql.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.
