Attached (assuming the list allows attachments) are files with the proposed schema, and queries to convert from existing schema to this one. As it turns out, there were a number of enums that it looked best to convert to simple strings, and others that looked best as reference tables. I've attached the conversion queries file mainly to provide the context on how the various columns are modified, as it's not obvious from the schema. There were a bunch of yes/no columns replaced with bools, and a few open/closed status columns that I've replaced with a boolean is_open.
You may notice that instead of doing some mapping with the season enum in the leagues table, I have suggested replacing it and the year column with open and close dates. This is something I was thinking of doing anyway, as TUC now has somewhat overlapping seasons (e.g. the last of our "fall" indoor seasons is just about to end, but at other facilities the "winter" indoor season has already started). By making this switch, we can eliminate the "current season" system setting, and just always use the current date to figure out which leagues are currently running. Greg. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Dave O'Neill Sent: Friday, January 08, 2010 7:43 AM To: Development discussion for the Leaguerunner league management system Subject: Re: [Leaguerunner] Converting Leaguerunner to CakePHP On Thu, Jan 07, 2010 at 06:17:18PM -0500, Peter McAlpine wrote: >My understanding of the day field would be to do two things: >1) Serve as part of the description of a league - Thursday Rec, Mon/Wed >Competetive, etc. >2) During gameslot creation, only allow new gameslots to be assigned to >leagues which operate on the days of the new gameslots. There's also a third use -- to make it easy to run queries against the database for day-based info. For example, pulling a list of all players who play on leagues with Tuesday games. > for use #1 - this can be accomplished just as easily as a string, or > included in the league name field. I agree. > for use #2 - I put forward that this isn't really required. It seems to be a > sanity check to ensure the user doesn't make a mistake and schedule > something on the wrong day by accident. I'm not a big fan of this. It's > confused my league admins before that they needed to first set the 'day' > field by editing the league before creating gameslots and making them > available to that league. The workflow for adding gameslots can probably be improved without removing this. > If the 'day' field or equivilant is deemed as being necessary, then the > options appear to be (none of which I'm a big fan of): > a) add a table with league_id and day fields (one-to-many), one row per > sechudle-eligible day per league. I would say this choice gives database > integrity while increasing program complexity > b) boolean fields for each day - simplistic but not very rhobust or > nice-looking database schema or code. I like a) much, much better than b). Cheers, Dave _______________________________________________ Leaguerunner mailing list [email protected] http://www.dmo.ca/cgi-bin/mailman/listinfo/leaguerunner _______________________________________________ Leaguerunner mailing list [email protected] http://www.dmo.ca/cgi-bin/mailman/listinfo/leaguerunner
