Re: The best way to solve a problem - Mailing list pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Hey,

On Tue, 2020-02-04 at 18:14 -0700, David G. Johnston wrote:
> On Tue, Feb 4, 2020 at 5:36 PM Nikolai Lusan <nikolai@lusan.id.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.

This is good to know, I have been using PGModeler to build and verify the
DB. I was putting things in schemas as a form of logical separation for the
next person who comes along to admin this solution (or migrate data from it
to another solution) sometime down the track.


> 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.

A bit more disclosure, this is for an Eight Ball (pool) association. The
team based matches are a 16 frame round robin match (nominally 4 players
per team, but there is the possibility of up to 6 players used by a team in
any given match). The 2 forms of singles are round robin (potentially with
multiple pools of players and seeding of players). Having match data for a
specific division is essential, as are the individual player stats.


> 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.

With about 300 players per year and 26 team rounds + 7 singles tournaments
the possibility of it growing is there, but I agree not really large enough
to matter. Having data around for previous years tournaments is of use if a
player stops playing for a while and then needs to be handicapped again,
also for records on the website. This is why I was thinking of putting
things in one table per tournament per year (possibly two tables for
keeping track of teams and fixture dates). I found a stackexchange post
from a person looking to do something similar in MySQL that a total of 3
tables - but I think I want something more extensive than this.


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

Yeah, but I am doing some forward planning right now. Some features done
now won't be used for a while (there are a couple of older committee
members resistant to change, and some others who want to come into the 21st
century.).

- -- 
Nikolai Lusan <nikolai@lusan.id.au>
-----BEGIN PGP SIGNATURE-----

iQIzBAEBCgAdFiEEVfd4GW6z4nsBxdLo4ZaDRV2VL6QFAl46bCoACgkQ4ZaDRV2V
L6QkBA//Tx/lgiIC8qbXqFPQ2UjeOdNwoO3ArknHCy8Mxwtiy7rtX6sfNxr206jr
wfFpAENuoBapssrYbVyyEvGzV5fIxwPYZAb71PxA7MtyW5m9RZKgiQlKIXam5gXc
NVjoT35KGsjlavD5skq8aqQNblJBZ7fFeWh8KpolliVrahh9umO6JEFuq/NK79PY
WF73qKGhy24ulHQsfxQFEvw3BWwWN7l9Xk4zFJbzFrni2XZNQxOg76k67RQJX8rC
1LxIJcCEHo1mWMpBAul64705OEZmjPH71f27yBKM2gDSgpDbdHlP2QxGAooYjZ8y
CmTZ5fmU3e87T4mHIFBkPNH2jhPZ50C4c3l90TEjdGarmVSvmXoGaIW7K7B07P+8
1WMU/a03UViEHZbD4idXSmmmr6oFdqdI55mg+72B2EWtjbu+2Cp58x7gD20KT2nC
ukDqd4+kZSEtRCgyICenCwI1Zt3nlMS7jPFSZpbvtzCLbFj0XmM2Hft02IIvo4E1
3J6wKmq+yQ/u4uT7g4iXTVUFR5WSNLQ2m3DbIjBjDYfQC/hsCcwk6MNC58Gp+j7U
gJU4i3BntdSf8pKpGjoYnl1N7qqQLlFEQE0oC0iCCkCcyd5/bx9efEiJOcexjJdn
x82QEWhFj9VlI5MDFH/Qdk0SrVl3hSwb8I3moON60iqSbDnovtE=
=vHp0
-----END PGP SIGNATURE-----