This should be an easy question because i am a database noob . I built a text based game and my players are wishing for some statistics on how they play and what they do. I have thought of two ways to do this. one way was to add more fields to the user table. It already holds about 70 fields for regular much needed data like time stamps, security stats, password and username etc. basic per user needed stats. The stats I came up with will require 100+ new fields. Now, If I add those fields to the existing user table and call the specific data per user when they visit their "stats" page will it be slower then my second Idea of creating a new table that just holds stats. I do not use the wildcard query in any of my mysql on any page if i went with a new table I would need to add code to add the user to this stat table on signup and just run a script for the existing users to put them there. It would be easier for me to use the existing user table but with 70 fields so far im worried it would make the queries slower because of the amount of fields it will need to go through to find specific data. Now, assuming my code is optimized, which would be better...a new table, or use the existing one...or does it matter? Thank you
100+ new columns for statistics? I am afraid to ask, but can you post the table structure and the proposed new columns? I will be able to provide specific assistance. If you cannot post the structure or columns, I would recommend a new table.
The structure will be int(20) default 0 for every field, key will be the unique user ID assigned to the user on signup and the engine will be MyISAM. The names of the fields are irrelevant to my question sorry . I have decided to make a new table after doing some personal research. Thank you for the replies this forum has helped me so much in the last few years.
It's a lot cleaner just to use another table PLAYER_STATS. In that table make a field USER_ID, which should be the same as the USER_ID field in the USERS table. That way it makes it very easy to match them up, do lookups, etc.
The names are relevant to the question. For example, if you have defined stat_1, stat_2, stat_3, etc. or some similar strategy we can assist with the design. Oh well, I guess another table with 100 columns is the ONLY way to go.