Hello, Part of my OVFPS app (for my daughter's softball league) is to provide games schedules in-app. Currently that's being done via a PDF file, but a database is the obvious solution to presenting schedules in a much nicer way. But I'm struggling with how to design the database itself. I was originally thinking a single large table, but it occurred to me that because my queries would return the same results twice, because a particular team appears on two different schedules for the same game (each team has a schedule). Here are the fields I was thinking of using: ID Division Team # Opponent Date Time Field # So, as an example of how it would appear in the Excel spreadsheet (and PDF version of it): * March 23rd, at 6:00PM, 14U 1 vs. 2 Field #1 In my single table scenario, since both Team #1 and Team #2 would have data for a game on March 23rd, any query for, as an example, Team #1 would return two results. I'd love to keep this as simple as possible to reduce the amount of PHP coding I need to do in order to run a query. A single table would be awesome, but maybe not the way to go? Anybody have any thoughts on how to make this work? Thanks! Mark

I may not be understanding your question correctly, but what if you only query "Team #" for "Team 1", then you should only receive 1 result per week (not 2).

Hmmm. Maybe I'm not thinking about this correctly, and I should just try it. Here's my thought process: * Team #1 has a game on March 23rd vs. Team #2 * Team #2 has a game on March 23rd vs. Team #1 That's two records that have Team #2 in it for the same date. So, if I do something like "SELECT * from 'Team#' were Team# = Team1," wouldn't that give me two results for the same game? I envision presenting the results from a couple different queries, including games for a particular date, and all games for a particular team. Mark

Wouldn't this get what you want: SELECT * from TeamTable where Date equal "2/23/2013" and Team# equal "Team 1" and Opponent# not equal "Team 1"

I don't suppose there is a 'game number' field? That would simplify it a bit... Cheers! -- Smug

Hi Mark, I'm just thinking about this poser and I have a couple of questions - how many teams are we talkng about in the softball league? Do you want a personalised calendar for each team or a general one for all the teams?

Hey Smug - I don't have a game number field, but that's something to consider. Alan - There are 4x6U Teams, 6x8U Teams, 7x10U Teams, 6x12U Teams, and 3x14U Teams. Mark

Hi Mark, so if I understand it, there are 5 leagues and the biggest league has 7 teams and you have all the data in a spreadsheet. It feels like a static html approach would do the job, (see this as an example http://www.bbc.co.uk/sport/football/premier-league/fixtures) or maybe in a vertical accordion style? (check this one on my site http://www.limetime.co.uk/fun-apps/f1-quotes/f1-calendar ), or maybe pump everything into google calendar could work? I could be missing the point here, I don't see the benefit of going to a database will help, maybe it is linked to a particular style you have in mind?

Just noticing... If you were to query 'team 1' there would only be 'one' of each record in the results, because 'Team 1' is the query for the 'Team #' field, and not the 'opposing team' field. If you're just querying results for 'a' team, you should be good. If you're going to query 'all' records, I'd try and split up the results and sort it by team, so that yes, you'll have 'somewhat duplicate' records, but they'll be in a relevant, non obtrusive location in the data. Cheers! -- Smug

or just query for a particular date/time and field #. You'll get two results. Ignore the "opponent" field and know that the "team #" in both results as the teams playing against each other.

What I really want to be able to do is query by team. My team is 14U#1, so I want to be able to pull up a list of all our games, including dates/times/location/opponents. I'd also like people to be able to pull up all the games on a particular date, so they can see who's playing, and immediately find out if they have a game or not. All the feedback thus far is appreciated! I'm going to set up a single table tonight, throw in a few records, and see what I can make work. It's very possible it's easier than I'm making it. Thanks! Mark

A little late to the conversation but something worth considering... Database design is always tricky. Always. What appears obvious today is not necessarily so in the future. Oftentimes the very near future. This just means these discussions are good to have so lots of different angles and gotcha's can be considered before settling on a design. In the good ole' days (of slow computers and even slower bandwidth) it made tons and tons of sense to work towards a normalized data-model. The benefits of this are numerous. BUT, there are drawbacks too. Drawbacks include complex data-schema's, difficult queries to write, even more difficult queries to tune, data that's not very human-readable. Think foreign key's and id's and how you can't necessarily know what each "id" represents without a deep understanding of the schema, model. The trend seems to be moving towards a better balance between normalized and maintainable / human readable. This seems like a great opportunity to consider JSON. My favorite data format, wink :-) It's my suspicion that this league will never have more than 50 or so teams (sounds like 40 today) in any given season. Assuming each team plays 20 games, we're only talking about 500 games or so (1000 / 2 because games are always two teams, LOL). If this is the case, a complex data structure seems excessive when compared to the ease and simplicity JSON represents. Consider this: What would happen if you were to try to pull this off with a table like the BT_items table that comes with the self-hosted package. This table is perfect for this type of thing because it's super easy to understand. It supports Mark's idea of one table holding all the data. Which of course adds even more to the simplicity concept. Think backups, exports, portability, etc. Rows (items) could be any type of thing. And, as the app evolved into something more sophisticated, the data-structure would not need to change. It's all about the item type. Something like... league_items.id = "auto increment number" league_items.guid = "something unique" league_items.nickname = "team name, game name, whatever" league_items.typeType = "team OR game OR player OR whatever, anything's possible" league_items.jsonVars = "{this is where the magic comes in and is endlessly flexible}" league_items.dateStamp = "date stamp of record created" league_items.modified = "date stamp of modified date" This is a ridiculously simple example but that's the whole idea. Right? As you can see, the combination of "itemType" and "jsonVars" is the most important thing to consider. The itemType tells you app what to expect in the jsonVars data. Something like... Row 1: itemType = "team" jsonVars = {"id":"U14#1", "type":"team", "nickname":"U14 #1", "color":"green", "coach":"Mark", "mascot":"Diamond Girls", "imageURL":"cool image of team"} Row 2: itemType = "game" jsonVars = {"id":"game#1", "type":"game", "nickname":"Saturday 04/23/13 8:00 AM", "homeTeamGuid":"U14#1", "visitorTeamGuid":"U14#3", "field":"Lighthouse Ballpark", "date":"04/23/13", "time":"08:00:00", "umpire":"John", "mapAddress":"123 Elm Street, Anytown USA"} Row 3: itemType = "player" jsonVars = ...you get the idea. At this point all the data is easily represented in the single table and everything is easy to understand when browsed, backed up, whatever. And, it's flexible because you can arbitrarily introduce new key:values down the road (in the jsonVars field) without worrying too much). But, what about queries and query performance? I would argue that queries are EASY when it's setup this way and performance won't matter much. Why? Because you'll only have 500 rows or so. About any machine on the planet these days can load 500 rows into memory much much faster than the app needs and there really won't be any reason to agonize over tuning, indexing, etc. Heck, you could pipe 500 rows of JSON text into an app without the user noticing much - even over 3G. Get team info for one team: "SELECT * FROM league_items WHERE guid = "U14#1"; That would return all the "team data" so you could do something useful with it, like show it on the screen. Get info for every team: "SELECT * FROM league_items WHERE type = "team"; Get info for every game: "SELECT * FROM league_items WHERE type = "game"; There immediate problems I can imagine are if you needed to query against the team color, game time, player uniform number, etc. Because these values are stored as json and not as individual fields (which make it easy without joins, foreign keys, etc) you will have to get a little creative. Then again, if you've only got a small amount of data to deal with, it would be trivial to handle this filtering / sorting client side, like in the app or on the webpage. //get games (all of them!) "SELECT * FROM league_items WHERE type = "game"; //look at all the games and process the data as needed on the client, pseudo code... while($row = mysql_fetch_array($qry)){ //json string from database... $jsonString = $row['jsonVars']; //inspect elements in this json to do something with it... if($jsonString != ""){ $json = new Json; $decoded = $json->unserialize($jsonString); if(is_object($decoded)){ if($decoded->{"homeTeamGuid"} == "U14#1"){ //this is a game for our team... } } } } As you can see, it's a matter of weighing the pros / cons of what you're trying to simplify. The data-schema or the app. I like simple and flexible data and rely on the app to carry as much logic as I can. There are arguments for the other way around of course and ultimately you'll need to decide what's most comfy. Looks like I better go earn some points, somebody passed me!!!

Wow! That is some serious food for thought, and I'm going to have to digest that a bit. I haven't ever looked at the BT_items table, but it looks like I'll be spending some time in it tomorrow to understand now this example works. I totally like the idea of super simplicity and extensibility, so this seems like it might be a good route to take. I think you're correct about the number of teams in the league, so it seems like a good approach. Thanks, David, for taking the time to provide some great feedback! I'll lay off the forum for a few hours so you can catch back up in points! ;-) Mark

Yup, BT_items works this way but does have a few more columns (fields) to hold some commonly queried values. You could do the same thing. Like for example you could have a gameDate column that was oftentimes null. If it was not null then for sure the itemType would be "game" Like I mentioned earlier, it's a matter of balance. I'm thing one table, grab all the rows, loop through them and act accordingly on the app side of things (app could be webpage, php script, ios, whatever). The next step will be consuming this data. You'll probably end up just writing one "processor" script that accepts a command in the URL like... mydomain.com/getData.php (returns nothing) mydomain.com/getData.php?command=getGames mydomain.com/getData.php?command=getTeams mydomain.com/getData.php?command=getTeam&teamGuid=U14#1 getData.php would have a switch statement to run the appropriate query. 1) get command 2) run query 3) echo data to screen (back to app) 4) exit(); App screens could point to the same URL with a different command.

This would be a really cool webinar! There are so many things we could do with this knowledge.

OK, I think I got something that meets my immediate needs. I didn't go with @David's suggestion...not because it wasn't absolutely brilliant, but because it is a bit advanced for me at the moment. Here's what my table structure looks like: `ovfps`.`schedule_2013` ( `id` , `game_date` , `game_time` , `division_id` , `home_team` , `visitor_team` , `field` , `home_away` ) And here's a sample query that gives me all the games for a particular 10U team: SELECT * FROM `schedule_2013` WHERE `division_id` = "10U" AND ( `home_team` = "2" OR `visitor_team` = "2" ) It's returning results exactly as I was hoping for! Certainly not a super optimized table, and probably tons of better ways to do it, but it's working for me at the moment. Thanks to everybody for their feedback...it gave me lots to think about! Mark

"It's returning results exactly as I was hoping for" Ultimately the only thing that matters! LOL. Cool, work from this and extend / adjust as the application matures. Or, think about next year today and make a simple change to the table name so your code isn't querying something that doesn't exist next year? Instead of tables for each year (and a query that needs to know what table to query, depending on what year it is), you could change your table name to... 'ovps.schedule' instead of 'ovps.schedule_2013' and then do... SELECT * FROM `schedule` WHERE `division_id` = "10U" AND ( `home_team` = "2" OR `visitor_team` = "2" ) AND YEAR(game_date) = YEAR(NOW()) That would return all the games for the current year. You could easily do... AND YEAR(game_date) = 2013 The idea is that it's easier to adjust the WHERE clause than it is the table name. No sense in having to create a new table each year, right? Your way will work, just more ideas to consider, all will be well with whatever solution you go with :-)

Just a quick thought - Do you want to include the scores/results as well?

@David, Yeah, that makes total sense! I tend to think a bit more compartmentalized, wanting to keep 'sameness' like year together, but no reason not to do it as you suggest. I doubt my table will ever get so large with old records that it would ever slow down query response. Plus, I can always purge old records. @Alan, Good thought. We don't formally keep a record of W/L because this is a recreational league, but I may bake it in for my own use! Mark