- This topic has 47 replies, 5 voices, and was last updated 17 years, 9 months ago by
.
-
Posts
-
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,
KyleGood input once again Kyle
” title=”Cheesy” /> now its time for me to explain a few thingsHaving a seperate table for position covers the multiple positions thing, so for each position they play on a team etc another entry is added to players_positions. By ticking 1 or more boxes it will add one or more entry’s stating that users ID the team they will be playing that position for “if applicable” and the position.
With finances I was not sure if there would be a need for it to be linked back to a tournament or game as some finance items may not be related to one, this is why each one has a “Description”. But I see how it will be helpful, I will add a link to tournaments.
A player can be in any number of teams. Players are placed into teams before the tournament by organizers or by choice (However things are run for that tournament), so there is no limit as to how many times a player is entered.
I agree with the extra email contacts, I will add that also.
Teams do not need the manager, coach or ass. coach and if its changed its changed for that “Team” so each entry of a team is changed.
“Time” referees to a UNIX timestamp, it has no specific format.
Venue is a good idea, I will add it.
I had a discussion with Ryan about the competition thing, the reason why I have the option to add teams directly to a competition is because not all competitions will have more than one devision, in the case that a competition only had one devision the organizer entering the competition would be required to specify a devision for a tournament with no divisions?
Instead it has the option to have a devision less competition or one with devisions.
If a competition has more than one devision it will ignore the “completions_teams” table completely and look in “divisions_teams” hence the double up there.
• period1_start
• period2_start
• period3_startThese are for another underlining possiblilty of the program, To speed things along for the scorers I’m looking at making a scoring program that will work with the database to make there lives easier. The period starts would allow a detailed time line of how the game went for players to view after the game.
[quote:van2xuab]
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. [/quote:van2xuab]Good question, it comes down to efficiently and size of MySQL and the database, yes thats the way about 80% of people who have not worked with any type of SQL database for a very very long time would do it. But… By adding 2 extra tables that arn’t going to be used every time you are adding to the size of each entry. By using a separate table assists are only entered when they happen instead of when they don’t happen also. ( Things shouldn’t be left blank as a rule of thumb, but sometimes its unavoidable ).
[quote:van2xuab]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. [/quote:van2xuab]
I see what you mean, but people would not have to enter it manually, I was thinking here it would just be added by the type of penalty any way. So I suppose there isn’t a need for time but instead a new table for penalties and there times.
Did not know about the period thing, will add something now.
Also the time thing is another fancy thing that would be included in the scoring system, when they clicked for a shot at the net it would record the time as well with no extra effort for a detailed time line of the game” title=”Cheesy” />If manually entered the times would be unneeded
Hope this clears some things up.[/quote]
so you still want me to see wat stats i can come up with this week or some one else doing it cos i am uber busy so i could do it possibly tommorow night, but otherwise if someelse wants to do it, that would be sweet.
btw goalies need to know save percentage and GAA, goals against average
"Michael":ugvaqfyg wrote:With finances I was not sure if there would be a need for it to be linked back to a tournament or game as some finance items may not be related to one, this is why each one has a “Description”. But I see how it will be helpful, I will add a link to tournaments.[/quote:ugvaqfyg]I can’t think of another area of finances that the club collects that you’d want to track in this system. Possibly registrations, but the registration amount is fixed, so I’d just have a tick box. I can’t think of a reason we’d want to track registrations in previous years, I’d say that it should be a tick box which gets blanked by a button by the admin at the beginning of the year.
The other money we collect relates to practises and outside tournaments, and I can’t think there’d be anything useful in putting it in this system.
"Michael":ugvaqfyg wrote:A player can be in any number of teams. Players are placed into teams before the tournament by organizers or by choice (However things are run for that tournament), so there is no limit as to how many times a player is entered.[/quote:ugvaqfyg]My point is, there’s things that don’t change about teams – name, top color etc. There are other things about teams that do change – the players in them, manager, coach etc.
Take the Beasts for example. The DIHL beasts are going to have a slightly different team lineup than the SIHL Beasts. I would have done it by a ‘team’ table which indicated all the different teams that we can have. And then made a team_instance table, which links to the team table. Players, coaches etc, link to team_instance_id. So if you’re playing in both Beasts teams, you’d be in both instances. Ryan, who is playing Bears in the DIHL, only links to one.
If you put it in one table with multiple beasts instances then you’re both recreating data, and also you’d have no way to list all players who played for the Beasts this year, as they’d be in Beasts 1 and Beasts 2.
Also, it’d be really nice if we could link the cell phone numbers to a web sms sending service. Clicking a button and sending everyone a txt message would be brilliant. There used to be a free one of these in south africa but it closed down, but I’m sure they still exist.
"Michael":ugvaqfyg wrote:I had a discussion with Ryan about the competition thing, the reason why I have the option to add teams directly to a competition is because not all competitions will have more than one devision, in the case that a competition only had one devision the organizer entering the competition would be required to specify a devision for a tournament with no divisions?Instead it has the option to have a devision less competition or one with devisions.
If a competition has more than one devision it will ignore the “completions_teams” table completely and look in “divisions_teams” hence the double up there.[/quote:ugvaqfyg]
OK, if that’s going to work
” title=”Smiley” />"Michael":ugvaqfyg wrote:• period1_start
• period2_start
• period3_startThese are for another underlining possiblilty of the program, To speed things along for the scorers I’m looking at making a scoring program that will work with the database to make there lives easier. The period starts would allow a detailed time line of how the game went for players to view after the game.[/quote:ugvaqfyg]
Interested to see how it works, but remember that people need to run this and watch a game of hockey at the same time, so you might want to make it a removable feature.
"Michael":ugvaqfyg wrote:I see what you mean, but people would not have to enter it manually, I was thinking here it would just be added by the type of penalty any way. So I suppose there isn’t a need for time but instead a new table for penalties and there times.[/quote:ugvaqfyg]Yeah my point is that it doesn’t need to be stored.
"Michael":ugvaqfyg wrote:Also the time thing is another fancy thing that would be included in the scoring system, when they clicked for a shot at the net it would record the time as well with no extra effort for a detailed time line of the game” title=”Cheesy” />[/quote:ugvaqfyg]
Again, I’m not sure our scorers would want to do that, so you might want to make it an optional feature.
We could also put in the feature where they list who was on the ice when goals were scored, and figure out plus/minus for players, but there’s no way they’re going to cope with that. They’re often on their own in the box there, running scoring, timekeeping, and penalty timekeeping.
They sometimes use a computer in there to connect to the scoreboard. It’d be nice if you could interface your system with the software that runs that, but that might be a pipe dream.
It would be no extra burden on the scores, by all means im working on simplifying the system for them, it would be a simple point click enter player number and done.
Finances I think is something we can work on later as its a Program in its own. As for registration I did allow for that in “Players” where it says diha_registered, this would be where the “admin” ticks that the player has paided there registration.
If the people handling the money did find it useful it could be used to let people know they have not paid for practices or games.
As for the sms thing, not something I know a lot about but I’m sure its doable.
[quote:1cyddj5t]My point is, there’s things that don’t change about teams – name, top color etc. There are other things about teams that do change – the players in them, manager, coach etc.
Take the Beasts for example. The DIHL beasts are going to have a slightly different team lineup than the SIHL Beasts. I would have done it by a ‘team’ table which indicated all the different teams that we can have. And then made a team_instance table, which links to the team table. Players, coaches etc, link to team_instance_id. So if you’re playing in both Beasts teams, you’d be in both instances. Ryan, who is playing Bears in the DIHL, only links to one.
If you put it in one table with multiple beasts instances then you’re both recreating data, and also you’d have no way to list all players who played for the Beasts this year, as they’d be in Beasts 1 and Beasts 2.
[/quote:1cyddj5t]Ok I see, I think where im getting confused here is that in Dunedin we have “teams” for competitions that always have same name, but really they are not the same team, they just have the same name because thats the name on the tops we have.
In your example, I would see it like this. Create two teams rather than one “Beasts (DIHL
” and “Beasts (SIHL)”, Keep in mind this “program” could help out other rinks around New Zealand that don’t use the same “grouping” method we use in Dunedin because we have tops with those team names.Beasts in that term is a “Group” of teams, not a team. Yes? No?
Thanks heaps for your input kyle it really helps when thinking things through as two minds are greater than one. and thanks for the score sheet
” title=”Cheesy” />[/quote]"Michael":govyt3o2 wrote:Ok I see, I think where im getting confused here is that in Dunedin we have “teams” for competitions that always have same name, but really they are not the same team, they just have the same name because thats the name on the tops we have.In your example, I would see it like this. Create two teams rather than one “Beasts (DIHL
” and “Beasts (SIHL)”, Keep in mind this “program” could help out other rinks around New Zealand that don’t use the same “grouping” method we use in Dunedin because we have tops with those team names.[/quote:govyt3o2]I guess my concern is (getting into database theory) is that if you create two different teams, you’re recreating information and storing it twice. If you have a table of teams, or maybe even call it ‘tops’ (Beasts, Sharks, SK8 etc etc), and then another table for each instance that team occurs you’re avoiding doing that.
If you think about a database in which the Beasts might appear in SIHL, DIHL (twice), Easton Cup, Erewhon Cup, and then maybe be used by other people (midgets or peewees for their leagues), you might be re-creating that information about the Beasts ‘tops’ six or seven times.
You might be able to help the team management out if you can ‘recreate existing team with new instance’ button, and then modify the roster. Say for the beasts, where the DIHL teams and SIHL teams are similar but not exactly the same, it’d be good to have a button to make a new Beasts instance, copy all the existing info across, and then modify it to make the differences. It’d also be useful to recreate last years beasts team to make this years instance of the beasts team as well, rather then re-entering the info.
And now I’m coming around to having two tables, ‘tops’ and ‘teams’. Each team has one (hypothetically two, for home and away, but we don’t do that here) tops. There might be many instances of the team beasts, but they’d all use the same tops table entry.
Looks like I need to go learn something about databasing
” title=”Sad” /> I’m totally lost with all this SQL stuff.Ryan,
I maintain a SQL database but I really wouldn’t know SQL if I fell over it, someone else designed it I just keep it ticking over.
Database principles however, one of the actually really useful things I learnt in my computer science degree. Actually had to use it several times in my jobs since, though this is the first time I’ve used it in hockey.
This situation is dunedin specific because we share tops, But the teams are not the same!, just because the name is Beasts does not mean that they are the same team , if they were the same team they would have the same people. so creating and reusing the Beasts team would be silly as its not really the beasts team.
"Kyle":jzv1l6k3 wrote:And now I’m coming around to having two tables, ‘tops’ and ‘teams’. Each team has one (hypothetically two, for home and away, but we don’t do that here) tops. There might be many instances of the team beasts, but they’d all use the same tops table entry.[/quote:jzv1l6k3]That would be what color, and alternate_color are for. Having a seperate entry for Jersey means the person entering the team has to enter a jersey first? The team does not belong to the Jersey! The jersey belongs to the team
” title=”Cheesy” />"Michael":2vz95ss4 wrote:This situation is dunedin specific because we share tops, But the teams are not the same!, just because the name is Beasts does not mean that they are the same team , if they were the same team they would have the same people. so creating and reusing the Beasts team would be silly as its not really the beasts team.[/quote:2vz95ss4]Lots of hockey competitions share tops. I played in an inline tournament in Nelson where the womens redbacks team had to literally pull the tops off the smelly guys as they came off the rink. With no groom in inline hockey, they had to be quick too, as it was cutting into their warmup.
Also, we don’t do it here, but lots of hockey clubs have the same tops through all age grades/genders etc. So they might have 6 teams all wearing their club tops, and the teams are differentiated by ages/grades. If you’re planning to create something which others places could use, having tops as a table makes sense to me.
It’s the team name which differentiates the teams on paper, and the tops that differentiates them on the ice.Also, Beasts A and Beasts B could be very similar teams, so being able to create a copy of the team, and then edit it to put in the differences would make sense to me.
Also, you could pull up last years team, and make them this years team, and then pull out and add players who have changed. That would save work on my part, I know. I use filemaker a fair bit, and it has ‘duplicate record’ which I use a lot when half the information in the next record is the same as the record I just did.
My suggestion would also allow you to have a team named one thing, using entirely different tops, but still know what tops they’re using. Phantoms last year played in dark blue Aces tops.
You have to remember that we have to manage tops as well (particularly ensuring that two sets of similar colour don’t end up in the same grade/competition. The only way for the database to know that there are two sets of dark blue tops (Beasts and Aces) and two sets of green tops (Bullfrogs and Stars) is to enter them as the tops, not the colours. Otherwise the database would only list ‘green’ and we’d miss out on the ability to put two sets of the same colour, one in each grade.
"Michael":2vz95ss4 wrote:That would be what color, and alternate_color are for. Having a seperate entry for Jersey means the person entering the team has to enter a jersey first? The team does not belong to the Jersey! The jersey belongs to the team[/quote:2vz95ss4]Actually the jerseys belong to the club. So please put them back when you’ve finished sweating on them.
It would just seem simpler to me if you made a table which included all the tops that there are in Dunedin. Beasts, Sharks, John McGlashan Black etc etc. Then when you add a team you give them a name (eg. Beasts), choose a top, and enter them in the right competition (DIHL B grade). Why would you want to type in ‘dark blue’? We use the same tops every year, lets save ourselves some work by putting them in once. They don’t have to enter it first, all the jersey sets would be set up when you set up the system, and you add to it if you have more tops/teams come into the system.
Dammit, design your system my way!
Ok well the cloning of teams wouldn’t change either way, you would still be able to do it as thats done on the php side of things, and I can see where this will be handly now. It may be a little tasking on visiting teams if this system is used however as all the tops have to entered first.
Heres what ill do, I will add a new table with tops, the team name colour and description, and also the option to have a photo or picture, When creating a team you can choose the top, if a top isn’t there you’ll have to add it (this is where i could see the problems with visiting teams or national/international events)
Sound good?
- You must be logged in to reply to this topic.