Re: The best way to solve a problem

On Tue, Feb 4, 2020 at 5:36 PM Nikolai Lusan <nikolai(at)lusan(dot)id(dot)au> wrote:

> I
> am a member of a small sporting association that I am doing some technical
> stuff for, part of which is designing and implementing a DB for membership
> and scoring records.
>

[...]
The rest of that planning seems like a significant case of premature
optimization. PostgreSQL has built-in partitioning now but even that seems
like an unnecessary addition to your data model at this point. Trying to
create it manually using schemas is something you probably should just be
discarded.

A matches table with adequate category fields to classify the type of game
being played and its outcome seems like it should be sufficient. The
business logic you describe is something that queries on that table can
solve. Reading the detail a bit more you probably want a "match" table and
a "match_result" table so you can pre-load matches that you know are going
to happen and then insert a corresponding record with the outcome once the
match is complete. That said, NULL can be useful is this limited situation
as well.

Your needs regarding historical data are not fully clear but you can
generally consider either discarding old information or copying current
data into the match table so that you archive the known values at the time
the match took place. Again, I wouldn't worry about the duplication onto a
read-only table or the space that it will take - your dataset size doesn't
seem like it will be large enough to matter.

You can always make changes later as new requirements are added or
constraints such as size become more important.

David J.