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