Updating data in two tables at once

[shawhey]

Ok, so I'm not quite sure how to explain this, but here it goes:

 

I have table A that contains stats for all players in the NHL, and then I have table B with just a few players. These few players in table B are also in table A, but table B has more information on them. I want to take the stats for these players out of table A and put into table B, and I want table B to update along with table A every time those numbers change. How would I do this?

[joel24]

Why is the information duplicated in the second table? I would suggest normalizing your database and removing duplicate copies of data... i.e. a NHL player table, then a seperate stats table, or even 2 stats tables if required

[gizmola]

Like Joel, I would question why you would want to do that.  From a functional standpoint, you either need to do that type of activity through application code, or on the backend via a trigger.  Triggers are the typical answer.  With that said, why is it that you have a seperate table B?  If it's simply that there is more information, then you can handle that using a subtype table that has just the additional columns.  You would then add a row to this table only in the cases where you need it, but would not be duplicating or moving the data from table a.

 

Additionally you can relationally solve the problem of having "lists" of players, by adding 2 tables that make a many to many relationship between the "listtype" and "player" table that would let you put players in any number of arbitrary lists you desire them to be in.

[shawhey]

Sorry, bad explanation. This is for a fantasy hockey league. The two tables have completely different uses. Ok, so the table with all the NHL players, table A, is very simple. It has all the players' names, positions, goals, assists, etc. Some players can end up on multiple teams in a single season, so to avoid duplicates in the table, I created a separate table for each team (as opposed to having one large table of every player).

 

Table B is for each team's profile information, which includes 'franchise players'. These are the few players that appear in both lists. This is where the problem lies. I didn't realize this when posting initially, but I actually just need to put their names into table B, and be able to call on the stats of these franchise players, which lie in table A. Still though, I tried and couldn't do that. Here's what I figured I have to do:

 

Select their names from Table B.

Assign their names to a variable.

Close Table B.

 

EDIT:

 

Additionally you can relationally solve the problem of having "lists" of players, by adding 2 tables that make a many to many relationship between the "listtype" and "player" table that would let you put players in any number of arbitrary lists you desire them to be in.

 

That sounds like something I could use. Could you explain that any further?

Select their stats from Table A, where the name matches the above variable.

 

Does that sound right? I tried to do it but couldn't find a way to make it work.

 

[shawhey]

Screwed up my edit and put it in the middle of something and I can't re-modify. Hopefully you can make it out that last part.

[gizmola]

What is the primary key of the players table?  I'm going to assume it might be something like "id" or "player_id".

 

Here's the table structure:

 

CREATE TABLE list (
    list_id SMALLINT UNSIGNED NOT NULL,
    name VARCHAR(40) NOT NULL,
    PRIMARY KEY (list_id)
);


CREATE TABLE listPlayer (
    list_id SMALLINT UNSIGNED NOT NULL,
    player_id INTEGER UNSIGNED NOT NULL,
    PRIMARY KEY (list_id, player_id)
);

 

So you add a row in list:

 

insert into list (list_id, name) values (1, 'Franchise Players - 2010/2011');

 

Hopefully it's obvious that you designate a player as a "franchise player" by having a row in the listPlayer table with the list_id of 1, and the player_id for the franchise player. 

 

 

I'm not sure if I entirely follow you in regards to your structure.  When you stated that you have a seperate table for teams, I hope you didn't literally mean that you have a table for every team!  A similar type of design where you relate players to teams would be the right approach.  That table would also need to have "FromDate" and "ToDate" and then the columns for the statistics.  If you had that structure, it might make sense to just have a franchise player column in it.

[shawhey]

ahhh, I see now. Ok, the only problem I have with using a FROMDate and TODate - as opposed to splitting a player's production into separate tables based on each team - is that there is an archive of stats from this past season that are on Yahoo! as opposed to my site. We are moving to our own site, but I just don't want to lose more stats. I suppose I can copy the stats from their tables into my own, but it just seems like a hell of a lot of work compared to the current setup, though it would make things more organized and easier to access.

 

Thanks for the help. If you have any idea how I can get this information from Yahoo!, that would be a huge help, though I don't currently see any way besides copying the numbers over.

[shawhey]

also, do I create a new entry each time the player changes teams? For example:

 

playerid, playername, team, goals, assists, powerplaypoints, plusminus, listid, fromdate, todate

102, David Desharnais, Tre Kronor, 8, 8, 4, 2, 0, 01/02/2011, 03/06/2011

103, David Desharnais, je suis habitant, 0, 6, 4, -2, 1, 03/07/2011, presentdate

 

As you can see, he has become a franchise player for me (je suis habitant), so the listid is 1; however, he was previously on another team and is therefore not a franchise player for Tre Kronor. Am I doing this right? Or is there a way to change the team, fromdate and todate without inputting another entry?

[gizmola]

No you are right. You need a row for every player/team/time frame combination.

 

When a player changes team, you in essence close out that row by setting the todate to the day of the trade.

 

Also what I tend to do is make the todate of an active row be the largest mysql data possible.  These rows should be mysql DATE types so that you do not need to do date math inside your php code.

 

The only problem i see is that you ahve the full player name in there.  Players should be in their own table.  I assumed you already had this.  Then you would just have the player_id number in this table. 

 

As for getting the data from yahoo or any other place, seems to me to just be a screen scraping exercise.  There are other sources for data of course like:  http://www.hockeydb.com/

 

 

[shawhey]

Oh ok. So I'll have this table just for their ownership and status, and can actually remove their names and stats from the table. Then, I'd have lists of their game logs that would provide the player's stats which would then be assigned to their team based on the corresponding FROMDate and TODate. Correct?

[gizmola]

You can either have the stats in this table, or not.  You can have a seperate stat table but I'm not sure if that's really helpful for you here, since it seems the stats are the current aggregate totals for the player on that team. 

 

However, if you have detail game info in a seperate table, then yeah it makes sense not to duplicate that redundantly in this table.  As you say, use the from-todate range to get the sum() of the data in a query. 

 

 

[shawhey]

Cheers. I think I got it now.