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