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