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