Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2012
    Posts
    44

    Keeping history, one table for each update?

    Here's the situation:

    I download stats and information around 3-4 times a year from a site. It's players, teams, nations etc., and lots of stats link to each all categories. I store the information in some different tables in my database, such as:

    players
    teams
    nations
    etc.

    So far so good. But then an update comes. The easiest solution would probably be to simply replace the above tables with the new updated stats. But the problem is I want to keep these stats for future use (like graphic development of the individual stats).

    What's the best solution to this? The only thing I have come up with is to create a new set of separate tables. For example, year 12 version one could be:

    players12_1
    teams12_1
    nations12_1
    etc.

    And then when the next update comes, and the next after that, it would look like this:

    players12_2
    teams12_2
    nations12_2

    players13_1
    teams13_1
    nations13_1

    (I would need some kind of dynamic tablename query here as well, but I wait with that question in case of the above is a very bad way.)

    Now, it will be many tables...but I don't know how else to keep the information. Of course I could simply INSERT the new update, and have a column named the update (12_1, 12_2 etc.), but the problem is that a PRIMARY for the playerid won't be a PRIMARY anymore...

    Any help with this much appreciated, thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's wrong with simply adding the new stats to your existing tables?

    having multiple copies of essentially the same structures will eventually drive you mad

    not to mention the difficulty in writing queries ("like graphic development of the individual stats") that span all these tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by r937 View Post
    what's wrong with simply adding the new stats to your existing tables?

    having multiple copies of essentially the same structures will eventually drive you mad

    not to mention the difficulty in writing queries ("like graphic development of the individual stats") that span all these tables
    No, that doesn't sound good.

    But then I can't have playerid as PRIMARY key. Should I then have an AUTO_INCREMENT as PRIMARY key instead? Otherwise there won't be any unique values.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Rudy is right, ya know?

    How about a composite primary key of "playerid" and something like "datecreated".

    P.S. moved to Database Concepts & Design
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Haydn View Post
    But then I can't have playerid as PRIMARY key.
    of course you should!!

    there's only one table where it makes sense that you have playerid as the PK, and that's the players table

    when you get the new data, do an "upsert" operation, i.e. if the player already exists, update his data, and if he doesn't exist, insert

    in mysql, this is trivially easy to accomplish with the ON DUPLICATE KEY UPDATE option of the INSERT statement

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by gvee View Post
    Rudy is right, ya know?

    How about a composite primary key of "playerid" and something like "datecreated".

    P.S. moved to Database Concepts & Design
    Thanks.

    I googled and searched this site but couldn't find what a composite primary key is? What does that mean?

    (Sorry for posting in the wrong forum.)

    ------------
    Thanks r937!

    But if I update the players stats, I can't view his history later on, the stats won't be present anymore.
    Last edited by Haydn; 03-04-12 at 09:47. Reason: Didn't see the new post from r937.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    A composite key is a key that is made up of more than one column
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Haydn View Post
    But if I update the players stats, I can't view his history later on, the stats won't be present anymore.
    depends on the precise design of your tables

    presumably, the players table contains data only for the player, and player stats would be in a separate table, right? related to teams, most likely

    for example, look at wayne rooney's club stats here -- http://en.wikipedia.org/wiki/Wayne_Rooney#Club

    you wouldn't have all of that in the players table, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by r937 View Post
    depends on the precise design of your tables

    presumably, the players table contains data only for the player, and player stats would be in a separate table, right? related to teams, most likely

    for example, look at wayne rooney's club stats here -- Wayne Rooney - Wikipedia, the free encyclopedia

    you wouldn't have all of that in the players table, right?
    Oh yes, that's true. Well, I could divide the stats that are history-based and the ones that are more profile-based. As it currently is, the file I have contains all stats linked to the player, for example contract length, height, weight, rating, reactions, stamina, strength etc.

    That way I can have one table with unique player-ids and their profile, and one with the players stats. When new updated stats then appear, these can be inserted into the player stats table with a date-column or something similar.

    Does that sound ok?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •