On Thu, 1 Apr 2021 09:51:38 +0800
Glen Huang <hey...@gmail.com> wrote:
Hi,
If you are in a position to modify your design, I believe your problem comes
from this part :
> WHERE paid OR join_time > now() - ‘1h’::interval
which suggests that there is a 'paid' column being updated. I learned that the
proper way to structure a database to maintain membership and save a lot of
grief is the following :
create table seated_member(
name text not null,
...,
join_time date not null default now,
validity integer not null default 1
);
where validity is a number of whatever unit is appropriate; typically 'year'
for a club membership (newspapers use 'issue number' instead of join_time to
account for strikes, when no paper is issued). In your case, 'hour' I suppose.
All you need to do when the member pays is to update the 'validity' field with
the proper amount of units. This makes for very simple and efficient queries to
retrieve the data, and you only need to write :
WHERE now() < join_time + 'validity hours'::interval
to retrieve valid accounts.
Accounts expire automatically, deleting them can wait; it also makes it easier
to send reminders before the expiration date
--
Bien à vous, Vincent Veyron
https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres
d'assurance
