Thread: The best way to solve a problem
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Greeting database brains, Although I started my career with a short lived role as a database administrator, and over the years have created a few small databases of my own, I normally do most of my database things on DB's that have been created by other people - and even then rarely mess with the internals. However I have a small problem that I can't figure the best way to solve. 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 logic required in application to do the scoring system is under control, as is the few tables required for tracking memberships. The problem I have is tables for the scoring. Each year there are multiple competitions run, over multiple grades. The first team round robin competition of each year is used to determine which teams play in which grades for the main season, and to handicap players based on a win/loss percentage. I need to keep data around for historical purposes (I know this means ever increasing storage issues), but some things (like team home locations) can be kept in a table that has the data changed at the beginning of each competition. There are also two different types of singles competitions run each year. All players are members of the association, so putting all in one DB makes sense to me, since I can then easily reference players by their membership numbers (or the sequence number that identifies their entry in the main membership table). I am not sure if I should be setting up some kind of template table for the various competition types and then using it to create either new tables, or new schemas, for each competition (something named like <year>_<grade>_<competion_type>). I need to be able to track player statistics through each round they play (the number of rounds in each competition varies depending on the number of teams/individuals playing) for a variety of reasons, and for the team competitions I need to be able to track the results of each round, and the number of games played/won/lost in each round. I would like a solution where I can easily remove historical data should the committee decide that they only want to keep X years of records for these competitions. My initial thought was to put the tables for each competition into a separate schema, but I'm not sure if this is the right way to go. In fact I'm not entirely sure I am approaching this whole thing in the right way. Does anyone have any ideas that could help me create an elegant solution to this in a Postgres DB? - -- Nikolai Lusan <nikolai@lusan.id.au> -----BEGIN PGP SIGNATURE----- iQIzBAEBCgAdFiEEVfd4GW6z4nsBxdLo4ZaDRV2VL6QFAl46DhUACgkQ4ZaDRV2V L6S2yQ/7BneZvFaVAHhaM0Yb9Ttr9W73iBau2nZryfgo0yYNL8zdJlC89gMqese5 oLSJA32kuAo/v7G2RA7O+4UYI4/Jou2cHZNUQh17u+B88FQ/vxE96w2Fge8q+h/t hF5C8DObEnuNwfJGzi1VpIpHlyQicD9C2nD0skRLgBvLjQXHiG8SOW7+SBd5uo/r XrsBgr/fuDQM8hEm/FtHNbspUwXMF0Yrwn5so2EqvwbHhS/By0I0TC+2/77Vawkx 008hKadAXc746tc56HH3nyAd7cUhWxNmLVRtMUc5feylIJEEYcMpf7Ybyo5pgv3T 9IO2+dCl4zL3wgolkjMjt5ofHlIkA0fPKVFrsaaROFNgtwiKx1KvSrmA5qBIzjjW 36mLH4kVjVOEhu35F4J9kGLgXbkfgqTQGRn/AdxUN5RagqJSVRkv+dxNyRmjIIHe qDqmJg5G9s6K2Vt+/TU0RRAGIXbYy0dj+ZLX1DSpsFuqr90935IH1OlVhO2sD2z9 gX6MtgQ8zVfcn+Omj5rvcNUkIpw9hP+lYMTfBU8CW1IOHhxTaif9HOW7sqF/IrOQ 3gg2FYCsqGeGgTdrT8n4+4EFImNThNhd9oR6aZPvZ0b7Mos+X7/Mwm4gCTpZQCXU xhBpiVOErIG+DcVhjVnahLpapew+JboOWr0kwb0BIlKYxtgFrAw= =aIeT -----END PGP SIGNATURE-----
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.
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.
-----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-----
Your message
To: pgsql-general@lists.postgresql.org
Subject: Re: The best way to solve a problem
Sent: 05-02-2020 12:48
was read on 05-02-2020 14:51.