In an effort to move the discussion over here, I'll pull some of the recent emails in and start a thread.
=======================
Jon wrote:
Again, good work.
One thing that immediately comes to mind is tracking for players
who switch teams/leagues. There'll need to be a joined_datetime and
left_datetime for each. left_, if null, would of course indicate that
the player was still active on the team.
... because when I look at the PRG lineups, I see all kinds of
antiquated information! And I'm sure they're not the only ones...
=======================
Don't put any stock in the data you see in the sandbox. As noted, all of that data was pulled out of Paige Burner's excel doc and has not been verified for accuracy or, er, modernity.
That said, the skater=>team association table does contain an "active" flag. It would be trivial to change that to use joined and departed dates instead, and I like the idea. I'll throw it in the next mockup. I suspect we still have several changes to make to how these relationships are tracked before we have a solid schema.
=======================
Jon also wrote:
I don't know enough about your structure to know if this is
problematic for you, but I'd suggest that you allow for the creation
of multiple player records with the same ID (I assume that they're
currently auto-numbered) which could be subsequently filtered by the
date ranges in joined_datetime and departed_datetime.
If that's no good, then a column for "original_id" would be useful.
Player 300 on team X, with original_id of 300, could be joined to her
new entries on Team Y, Z, etc. regardless of their IDs... as long as
the original ID was properly filled out in every subsequent entry.
These could then be filtered by joined_ and departed_datetime to get a
better picture of her history.
It'd also be useful to have an alt_name column (or perhaps even a
table joined by original_id), since I know several people who've
skated by multiple names.
=======================
The world of derby is particularly troublesome in this regard. We've got skaters skating under different names, on multiple teams at the same time, switching teams, etc, and we need to keep track of all of it. I gave a lot of thought to these relationships and I'm glad to have some input.
Allowing player records with the same ID would make the normalization gods cry, methinks, but your core concern is a valid one. We must be prepared to deal with the madness of changes and multiple personalities. The `skater` table currently carries only the most basic info about a skater and I think that the entry in the skater table be the "original one and only". The schema is already pretty much set up to do this, but it does need to be changed a bit to cover all of the bases. Once a skater is in the system and gets an ID in the skater table, that's it. That record cannot/must not be changed. We can then use junction tables to keep track of her nicknames, numbers, teams, etc.
-G
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I get you. And yes, I could hear my boss yelling at me for that one suggestion when I made it. Which was odd because he wasn't at work yet. Which was good because if he'd been at work, he'd've been yelling at me for dicking around with non-work-related stuff.
I guess the reason I can't make more/better suggestions at this time is that I don't know the database schema and I haven't yet figured out how to locate it. I'm not familiar with SourceForge. What am I overlooking?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You're not missing anything. We don't actually have the code up on sourceforge yet! You can access the current db schema at http://derbymatic.leadjammer.com/SOURCE/derbystat_sql.tar.gz. That file will show you what the current sandbox is built on. That schema, however, is already obsolete. See my post in the developers forum....
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Part of what you're encountering is the fact that I've bridged two goals: developing a set of roller derby database tools, and learning about using Sourceforge to coordinate a dev effort. This would probably be a good time to point out that I am not an experienced project manager -- I am mostly a rabble-rouser :) If my goal of using Sourceforge is more of an obstacle than a help, we can certainly go another direction at any time.
That said, I plan to spend some time poking at it tomorrow, so hopefully I'll have this cvs'd up shortly. Or something. Stay tuned!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Is it possible that I haven't reinstalled Winzip since my last catastrophic crash? Apparently it's completely possible. That was nearly a year ago. Incredible.
OK so I realize that this is an old schema, but I have this to say about it:
I don't understand your _assn tables, since they're just indexes on other tables. That's the kind of "don't understand" that's at the theoretical level, probably has to do with training.
In a more typical "I don't get your naming conventions" way, I don't see what r_first and r_last are for. What data would they contain if they weren't so uniformly null?
I think the table names tend to make more sense without "j_" prefacing them, unless there's some reason for this that didn't occur to me.
In j_bout_team, what is the purpose of Home? I'd've thought Home vs. Away, but then it'd make more sense as a bit field than tinyint so maybe I'm misinterpreting.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- r_first and r_last (in the skaters table) are meant to be for "real first name" and "real last name".
- all of the tables prefaced with j_ and/or _assn are copies of each other. They were originally pure association tables (see below), but I've been adding to them when it makes sense. Yes, the j_ and _assn are redundant for naming. I usually use j_ for all association tables (I originally learned to call them "junction" tables) because it lets me see them together when sorting tables by alpha.
- Your assumption of the home field is correct: home vs. away. But MySQL only recently got a BIT datatype, so I'm used to using TINYINT(1), which is functionally equivilent.
- The assn (aka junction) tables are indeed indexes on other tables (with other metadata added, as mentioned above). This is the normal way to model many-to-many relationships in RDBs.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
In an effort to move the discussion over here, I'll pull some of the recent emails in and start a thread.
=======================
Jon wrote:
Again, good work.
One thing that immediately comes to mind is tracking for players
who switch teams/leagues. There'll need to be a joined_datetime and
left_datetime for each. left_, if null, would of course indicate that
the player was still active on the team.
... because when I look at the PRG lineups, I see all kinds of
antiquated information! And I'm sure they're not the only ones...
=======================
Don't put any stock in the data you see in the sandbox. As noted, all of that data was pulled out of Paige Burner's excel doc and has not been verified for accuracy or, er, modernity.
That said, the skater=>team association table does contain an "active" flag. It would be trivial to change that to use joined and departed dates instead, and I like the idea. I'll throw it in the next mockup. I suspect we still have several changes to make to how these relationships are tracked before we have a solid schema.
=======================
Jon also wrote:
I don't know enough about your structure to know if this is
problematic for you, but I'd suggest that you allow for the creation
of multiple player records with the same ID (I assume that they're
currently auto-numbered) which could be subsequently filtered by the
date ranges in joined_datetime and departed_datetime.
If that's no good, then a column for "original_id" would be useful.
Player 300 on team X, with original_id of 300, could be joined to her
new entries on Team Y, Z, etc. regardless of their IDs... as long as
the original ID was properly filled out in every subsequent entry.
These could then be filtered by joined_ and departed_datetime to get a
better picture of her history.
It'd also be useful to have an alt_name column (or perhaps even a
table joined by original_id), since I know several people who've
skated by multiple names.
=======================
The world of derby is particularly troublesome in this regard. We've got skaters skating under different names, on multiple teams at the same time, switching teams, etc, and we need to keep track of all of it. I gave a lot of thought to these relationships and I'm glad to have some input.
Allowing player records with the same ID would make the normalization gods cry, methinks, but your core concern is a valid one. We must be prepared to deal with the madness of changes and multiple personalities. The `skater` table currently carries only the most basic info about a skater and I think that the entry in the skater table be the "original one and only". The schema is already pretty much set up to do this, but it does need to be changed a bit to cover all of the bases. Once a skater is in the system and gets an ID in the skater table, that's it. That record cannot/must not be changed. We can then use junction tables to keep track of her nicknames, numbers, teams, etc.
-G
I get you. And yes, I could hear my boss yelling at me for that one suggestion when I made it. Which was odd because he wasn't at work yet. Which was good because if he'd been at work, he'd've been yelling at me for dicking around with non-work-related stuff.
I guess the reason I can't make more/better suggestions at this time is that I don't know the database schema and I haven't yet figured out how to locate it. I'm not familiar with SourceForge. What am I overlooking?
You're not missing anything. We don't actually have the code up on sourceforge yet! You can access the current db schema at http://derbymatic.leadjammer.com/SOURCE/derbystat_sql.tar.gz. That file will show you what the current sandbox is built on. That schema, however, is already obsolete. See my post in the developers forum....
Part of what you're encountering is the fact that I've bridged two goals: developing a set of roller derby database tools, and learning about using Sourceforge to coordinate a dev effort. This would probably be a good time to point out that I am not an experienced project manager -- I am mostly a rabble-rouser :) If my goal of using Sourceforge is more of an obstacle than a help, we can certainly go another direction at any time.
That said, I plan to spend some time poking at it tomorrow, so hopefully I'll have this cvs'd up shortly. Or something. Stay tuned!
Is it possible that I haven't reinstalled Winzip since my last catastrophic crash? Apparently it's completely possible. That was nearly a year ago. Incredible.
OK so I realize that this is an old schema, but I have this to say about it:
I don't understand your _assn tables, since they're just indexes on other tables. That's the kind of "don't understand" that's at the theoretical level, probably has to do with training.
In a more typical "I don't get your naming conventions" way, I don't see what r_first and r_last are for. What data would they contain if they weren't so uniformly null?
I think the table names tend to make more sense without "j_" prefacing them, unless there's some reason for this that didn't occur to me.
In j_bout_team, what is the purpose of Home? I'd've thought Home vs. Away, but then it'd make more sense as a bit field than tinyint so maybe I'm misinterpreting.
- r_first and r_last (in the skaters table) are meant to be for "real first name" and "real last name".
- all of the tables prefaced with j_ and/or _assn are copies of each other. They were originally pure association tables (see below), but I've been adding to them when it makes sense. Yes, the j_ and _assn are redundant for naming. I usually use j_ for all association tables (I originally learned to call them "junction" tables) because it lets me see them together when sorting tables by alpha.
- Your assumption of the home field is correct: home vs. away. But MySQL only recently got a BIT datatype, so I'm used to using TINYINT(1), which is functionally equivilent.
- The assn (aka junction) tables are indeed indexes on other tables (with other metadata added, as mentioned above). This is the normal way to model many-to-many relationships in RDBs.
forget winzip, man... 7-zip is your friend. GPL'd and it handles everything.
http://www.7-zip.org/
Pretty sure "j_" means it's a join-table.
The "_assn" tables seem a bit redundant.
Haven't figured out what "r_" is supposed to mean either.
If they're indexes then I get it. I just didn't realize MySQL doesn't support actual indexes.
If you're saying what I think you're saying, then no, that's not quite it.
MySQL does support several types of index, including strict foreign keys if you use the InnoDB engine.
A junction table is a table that uses the keys (primary indexes) from two other tables to handle a many-to-many relationship.