Forum Replies Created
-
AuthorPosts
-
KyleMember
OK, this is going to start getting seriously geeky soon. Or it already was when Michael posted that table structure I guess.
"Michael":1to34qya wrote:Table “players_positions”
• player_id
• team_id
• position[/quote:1to34qya]Just for this one, lots of people tick multiple boxes, or leave themselves open for forward or defense. So either include forward/defense as an option, or allow multiple options to be entered.
"Michael":1to34qya wrote:Tables “players_finances”
• player_id
• type (topay, payed)
• description
• amount
• timeTable “players_hired_gear”
• player_id
• item[/quote:1to34qya]For these ones here, I’d suggest you need to link them to a ‘entry’ or what might be referred to as a ‘player_instance’. By my reading of what you’re doing, a player is a constant feature. Michael has name, experience, links to address and phone details etc. By your tables above, we can track what Michael has paid and what he’s hired. However if Michael enters in multiple tournaments over time, we can’t track which tournaments he’s paid for. So I’d suggest each player has a one to many relationships with ‘entry’ or similar. Then you use an entry table to indicate which tournament they’re entering in, and which team they’re playing in in that tournament. I’d want to know what Michael has paid for this tournament, not what he’s paid over the history of his hockey.
Also note that it’s possible to enter one tournament more than once, if there are multiple grades or age groups. Some people play in both the A and B grade DIHL for example. So that would need to be possible.
"Michael":1to34qya wrote:Table “players_addresses”
• player_id
• street
• suburb
• city
• country
• post_code
• descriptionTable “players_phonenumbers”
• player_id
• type
• description
• area_code
• number[/quote:1to34qya]We sometimes get players with multiple email addresses, so it’d be useful to have a way to break them out into a separate table and have them. It’d be good to have the secondary email address as only ever useful manually – like we’d never want to automatically send an email to that address, but if we got a bounce we’d move it into the primary address.
"Michael":1to34qya wrote:Table “players_teams”
• player_id
• team_id
• numberTable “teams”
• team_id (Auto increment)
• name
• description
• color
• alternate_color
• manager
• coach
• assistant_coach[/quote:1to34qya]Again, teams might be used as multiple instances, and multiple times. So team should just include name, top, and color (bloody American spelling!). Anything that might change – manager, coach etc, should be in a team_instance table (which would surely link through to your competitions stuff below?).
"Michael":1to34qya wrote:Table “competitions”
• competition_id (Auto increment)
• name
• description
• start_time[/quote:1to34qya]I’m not sure if we’d do start time. I’d put ‘year’ in as a field, because that might be useful. In that instance a competition should be able to have two years, because a summer league would cross over.
If you wanted to think about this being useful for more than Dunedin, then you’d need to think about multiple venues.
"Michael":1to34qya wrote:Table “competitions_teams”
• competition _id
• team_id[/quote:1to34qya]Teams don’t enter in competitions, they enter in grades or divisions. We don’t enter the Beasts in the DIHL, we enter them in the DIHL B grade. I would suspect the easiest way to do it would be that if a competition is open, it just has one division called ‘open’ which then gets ignored in all the output. I’m not sure you need this table.
"Michael":1to34qya wrote:Table “devisions_teams”
• devision_id
• team_id[/quote:1to34qya]I don’t understand why you need this one either. It’s just a linkage, why not have a one-to-many relationship between the table before and teams? I’m no SQL expert though, so maybe you just need to. And, that’s not how you spell division.
"Michael":1to34qya wrote:Table “games”
• game_id (Auto)
• competition_id
• devision_id
• description
• team1_id
• team2_id
• scheduled_start
• period1_start
• period2_start
• period3_start[/quote:1to34qya]I’m not sure if we record the last three bits of data. We would need to have scorekeeper, referee, linesmen, timekeeper.
As a note on those last ones. I’m not sure if you’d want to add them to the players table, or make some other table of ‘officials’. Just to prevent having to type their names in, you should be able to look up people like Jackie, Joyce, and all the referees.
"Michael":1to34qya wrote:Table “goals”
• goal_id
• game_id
• player_id
• team_id
• timeTable “goals_assisted”
• goal_id
• player_id[/quote:1to34qya]Why wouldn’t you just put two more player_id fields in the goal table for assists, and save yourself a table? They could be left blank if it’s unassisted.
"Michael":1to34qya wrote:Table “penalties”
• penalty_id
• game_id
• player_id
• team_id
• type
• description
• minutes
• time[/quote:1to34qya]If you have ‘type’ you shouldn’t need to enter minutes. It should look it up from a table, I can’t think that we’d want people manually entering how long penalties are. Minor, double minor, major, game etc are all fixed.
If you have ‘player_instance’ as I outlined above, you don’t need team_id in all these, as player_instance knows what team they’re in.
"Michael":1to34qya wrote:Table “shots”
• shot_id
• game_id
• player_id
• team_id
• time[/quote:1to34qya]For goals, penalties, assists, shots, you also need to record the period of the game that they fell in.
For shots, I don’t know of any league that records the time of shots – maybe the NHL does it. We would only ever count the shots in a period against a goalie. Which you’d compare with the goals in a period that goalie let in. So I’d remove this table and move the shots information into the game table, split by period.
Looking good,
KyleKyleMember"Chris":3nf9zn85 wrote:Wow, this database idea is actually giving me hot flushes….[/quote:3nf9zn85]OK. I’m not getting changed near to you next week. Too weird.
KyleMemberOh no, definitely go with the first one. Captures the true Chris.
KyleMember"Janey":263b157g wrote:This year Kyle you should be given you the wooden spoon simply so you can STIR a little more!
I dont mind if they forgot me as long as I get to play for them on the 22nd Im sweet..Hockey hockey hockey thats what matters.[/quote:263b157g]Yeah sorry. B grade is full. You’ll have to play A grade. Should have got your form in before… well before the competition started.
And world peace matters. Hockey is second at best.
KyleMemberif it’s just a stats management, then it should track goals, assists, games, tournaments/competitions, penalty minutes, penalty types, shots and saves. players should be able to be independent from their numbers between games, and yet retain their stats. Players should be able to be in multiple teams, multiple grades, and multiple competitions.
It should track individual career stats, game stats, and competition stats, and then the same for team stats. It would need to deal with overtime and penalty shootout situations.
If you really wanted to go for broke and make it a full tournament management software, then it should track names, addresses, phone numbers, emails, grades (A or , grade (the number we use to determine what grade a person can play in). It should track teams played in and tournaments. It should keep track of money.
then it should do single and double round robin draws for 4, 6, 8, and 10 teams. It should compute that draw both for a tournament style competition, and a weekly competition. It should do semi-finals and finals, optionally. It should keep track of points across tournaments
Everything should convert to useful html/css, presumably linked to a style sheet. Input should be able to be done offline, and you should be able to import material from spreadsheets or csv files.
If it sent out emails, that would be sweet. I’d also like it to be able to upload photos, and to create team and player pages, which included the stats and possibly a brief bio.
I’m sure I can think of more stuff, but that would get you started.
KyleMemberThe stats will be available from the first round once I remember to bring them in from home and pass them onto our statistician. Duh me.
KyleMemberAaron’s offered to do the stats for us this DIHL, but I don’t think he’s really sure what he’s going to do with them.
I agree, if someone could write a database to process statistics, which also did team and tournament management, it’s a gaping hole in the market waiting to be filled. Even a shareware one could easily make a few grand I’m sure.
KyleMemberDo you mean running as in, when a person scores a goal and it goes up on the scoreboard, the website has the stats live as they are entered in the score box?
Because that would be really hard, mostly because there’s no way that I know of to connect the computer they sometimes use in the scorebox to the internet.
I’d presume there is, or could be, software to do the rest of it, if someone was really keen.
I’d like every player to have a player page which got updated with stats over their career, listing teams they’ve played for and linking to pdf or digital copies of the scoresheets. With a pretty photo. But dreams are free ” title=”Smiley” />
KyleMember"Ryan":2zrm1zsd wrote:"Kyle":2zrm1zsd wrote:I emailed you a contact list yesterday evening. Did you get it?[/quote:2zrm1zsd]Nope, all I got was …
[quote:2zrm1zsd]Ryan,
I have some new contacts from the DIHL B grade. Do you want these now, or do you want to wait until I can add new A grade people as well.
Cheers,
Kyle[/quote:2zrm1zsd]with no attachments.
Ryan,[/quote:2zrm1zsd]
Hmm, I sent another one from the dihl email address. I’ll send it again now.
KyleMemberI emailed you a contact list yesterday evening. Did you get it?
KyleMemberNo I didn’t forget. I add people to the roster when I get their signed form. I haven’t been given one with your name on it – or indeed three other beasts. You’ll need to bring it on Sunday.
And stirring has two rs.
KyleMemberOoh yes, we’re under way again, with a new streamlined, smooth-as, DIHL. Four B grade teams vying for… y’know. Winning lots of games and gloating over the other three teams.
Let’s run through the teams:
John McGlashan: Defending champions Jonnies return to defend their B grade title. They’ve dropped a couple of top players out of the team, and picked up a couple of young’uns, but Leask in goal is an improving player and is going to be a big help to them. Jonnies work hard on their hockey and the kids have a bit of a profile up at the school, so they’ll be going all guns again. A little bit of settle down and play the game rather than the player, and they’re going to be hard to beat this year.
Beasts: Losing finalists last year, Beasts are back and just as strong. They’ll be looking to do one better this year, and they’ve picked up another ex-field hockey goalie to help them out – Neil Dudley, younger brother of 2006’s Mark. There’s a big lineup of solid players – Li, Sedgwick, Noble, Hareb, Mitchell and they’re my pick for the title.
Bears: A mishmash of Peewees, midgets, parents, odd-bod students and whoever else accidentally filled in a piece of paper, the Bears will struggle this year, but they’ll have great fun doing it. Children will laugh as their parents wipe out into each other, and they’ll play 100%, and might push a couple of the better teams at times. Watch for American exchange student Tess, DIHL regular Hellyer, Kinraid, and Roth the younger, who’s been cutting up the ice in the peewees for the past couple of years. He can score a few goals, and might clock up some assists as well.
Kings: Kings are back and looking solid as ever. A couple of new players, but a lot of the team that placed third in 2006 are still there. Watch out for King, Bodnardvik (is that spelt right?), and Auton, all of whom scored a fair bit last year. Goalie Williams has been around the scene for a while, and should be looking for some good numbers this DIHL.
Picks!
Yes, we know you all love it.
Jonnies vs Beasts: Kinda serious for first up, I didn’t plan it that way, honest! Teams are going to take the first week to settle themselves down, but I know the Beasts have been building for a big season, with playing in the Southern Ice Hockey League Senior Non-checking as well. Jonnies are a good team, and will be wanting a victory first up over their major rivals for the title. Key for Beasts – taking advantage of when they’re on the powerplay – good puck control, keeping the pressure on, and getting it on net, and putting the rebounds away. Key for Jonnies – keeping those opportunities down, and looking to their better players to make the Beasts goalie have a bad night. My pick: Beasts 5-3.
Kings vs Bears: I think both these teams are going to be off to a bit of a cold start, and it’ll take a while for them to sort their systems out and figure out who should go where. Key for Bears – working well as a team, they don’t have the skaters that can go coast to coast, but some of them might think they can. This ain’t peewees/midgets, this is big person hockey, and good passing and working on assists as much as goals is important. Getting players to the net and making good shots and putting away rebounds is key. For Kings, not so much. They’ve got some players who can cut up the ice pretty well, so they’ll be looking to get those players out in the open and away, and see how the Bears goalie copes. Special note for Peewees goalie Jaimee Wood, who’s fronting up and facing some serious offence coming her way, hope she has a good game. Kyle’s pick: Kings 7-2.
KyleMember"Janey":2tinn5r7 wrote:Hey I wanna play this DIHL can someone make sure the Beasts don’t forget me when regestering the team! [/quote:2tinn5r7]This might be more stirring, but the Beasts forgot about you when registering their team. ” title=”Wink” />
KyleMemberWell I’m not sure what further practices they’re planning to put into the schedule. The new things are the powerskating classes, one of them is going into Tuesday night, the other into Sunday morning, but as far as I know both those times are already set.
I urged a principle to be adopted of keeping practices away from time periods where people are playing games. So B grade games shouldn’t be on a night when practices are on that involve B grade players (peewees, midgets, adult beginners, high schools). Adult beginners refused to move however, so there’s a clash which I suspect doesn’t help either the beginners or the DIHL B grade.
If practices and games need to be on the same night, then they should cross over. So Prems practice on a night when B grade games are on, and Peewees practice on a night when A grade games are on. It’s mostly worked out that way, but not perfectly.
KyleMemberThe only practice on Sunday night is the beginners one, which finishes at 6pm.
-
AuthorPosts