On Tue, Feb 4, 2020 at 5:36 PM Nikolai Lusan <nikolai@xxxxxxxxxxx> 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.
