hyster Posted October 24, 2015 Share Posted October 24, 2015 I have a database I want to build, unfortunately it has about 320 records I need to insert per user but max user count of 100, this seems a massive amount of data for a single table row so 2 questions. what is the max practicable amount of records per table row ?? (is it based on column count or actual character count ?) am I best cutting it up into relevant sections? (pretty sure the answer is yes though) thanks guys Quote Link to comment https://forums.phpfreaks.com/topic/298803-database-design/ Share on other sites More sharing options...
Solution benanamen Posted October 25, 2015 Solution Share Posted October 25, 2015 You can have millions of records in a table. You should first learn Database Normalization and you will have a better idea how to do your tables. The amount of records you have wont even tickle a database. 1 Quote Link to comment https://forums.phpfreaks.com/topic/298803-database-design/#findComment-1524237 Share on other sites More sharing options...
hyster Posted October 25, 2015 Author Share Posted October 25, 2015 thanks benanamen, I wasn't concerned with the amount of data per se but the amount of data for each entry (row). ill read up on database normalization, cheers Quote Link to comment https://forums.phpfreaks.com/topic/298803-database-design/#findComment-1524243 Share on other sites More sharing options...
benanamen Posted October 25, 2015 Share Posted October 25, 2015 If you want to post a row of your data with headers I will help you sort it out, but you still need to understand Normalization. Quote Link to comment https://forums.phpfreaks.com/topic/298803-database-design/#findComment-1524244 Share on other sites More sharing options...
hyster Posted October 25, 2015 Author Share Posted October 25, 2015 this is a data dump for my account, max users is 100 accounts, data is coming from a games API. the column headers will equal the same for the data ID other when its repeated id use clan_spotted regular_team_spotted company_spotted all_spotted and so on or if i used a separate table for each section then keep them as the origanal name { "status": "ok", "meta": { "count": 1 }, "data": { "502438129": { "client_language": "en", "last_battle_time": 1445732920, "account_id": 502438129, "created_at": 1329221187, "updated_at": 1445732980, "private": null, "ban_time": null, "global_rating": 6209, "clan_id": 500020315, "statistics": { "max_frags_tank_id": 52561, "clan": { "spotted": 60, "avg_damage_assisted_track": 81.71, "avg_damage_blocked": 426.05, "direct_hits_received": 355, "explosion_hits": 4, "piercings_received": 270, "piercings": 237, "xp": 50890, "survived_battles": 43, "dropped_capture_points": 118, "hits_percents": 68, "draws": 7, "battles": 104, "damage_received": 142066, "avg_damage_assisted": 275.33, "frags": 46, "avg_damage_assisted_radio": 193.62, "capture_points": 167, "hits": 392, "battle_avg_xp": 489, "wins": 42, "losses": 55, "damage_dealt": 113955, "no_damage_direct_hits_received": 85, "shots": 575, "explosion_hits_received": 33, "tanking_factor": 0.31 }, "max_xp_tank_id": 8193, "regular_team": { "spotted": 13, "avg_damage_assisted_track": 83.73, "max_xp": 1332, "avg_damage_blocked": 334.55, "direct_hits_received": 59, "explosion_hits": 1, "piercings_received": 36, "piercings": 36, "max_damage_tank_id": 2849, "xp": 5905, "survived_battles": 6, "dropped_capture_points": 6, "hits_percents": 81, "draws": 0, "max_xp_tank_id": 2849, "battles": 11, "damage_received": 9621, "avg_damage_assisted": 133.45, "max_frags_tank_id": 2849, "frags": 7, "avg_damage_assisted_radio": 49.73, "capture_points": 0, "max_damage": 1613, "hits": 65, "battle_avg_xp": 537, "wins": 3, "losses": 8, "damage_dealt": 11993, "no_damage_direct_hits_received": 23, "max_frags": 2, "shots": 80, "explosion_hits_received": 0, "tanking_factor": 0.35 }, "max_xp": 2304, "company": { "spotted": 350, "avg_damage_assisted_track": 124.71, "avg_damage_blocked": 0, "direct_hits_received": 103, "explosion_hits": 0, "piercings_received": 77, "piercings": 75, "xp": 247376, "survived_battles": 187, "dropped_capture_points": 478, "hits_percents": 60, "draws": 6, "battles": 470, "damage_received": 290064, "avg_damage_assisted": 260.88, "frags": 285, "avg_damage_assisted_radio": 136.17, "capture_points": 1048, "hits": 1178, "battle_avg_xp": 526, "wins": 313, "losses": 151, "damage_dealt": 289651, "no_damage_direct_hits_received": 26, "shots": 1961, "explosion_hits_received": 5, "tanking_factor": 0 }, "trees_cut": 12089, "all": { "spotted": 13359, "avg_damage_assisted_track": 84.28, "max_xp": 2304, "avg_damage_blocked": 246.9, "direct_hits_received": 42528, "explosion_hits": 4419, "piercings_received": 31962, "piercings": 42247, "max_damage_tank_id": 9297, "xp": 9818758, "survived_battles": 5639, "dropped_capture_points": 10632, "hits_percents": 60, "draws": 296, "max_xp_tank_id": 8193, "battles": 18481, "damage_received": 15620244, "avg_damage_assisted": 288.14, "max_frags_tank_id": 52561, "frags": 15342, "avg_damage_assisted_radio": 203.86, "capture_points": 32787, "max_damage": 7782, "hits": 105063, "battle_avg_xp": 531, "wins": 9477, "losses": 8708, "damage_dealt": 18136701, "no_damage_direct_hits_received": 10566, "max_frags": 8, "shots": 176205, "explosion_hits_received": 1665, "tanking_factor": 0.28 }, "stronghold_defense": { "spotted": 0, "max_frags_tank_id": null, "max_xp": 0, "direct_hits_received": 0, "explosion_hits": 0, "piercings_received": 0, "piercings": 0, "xp": 0, "survived_battles": 0, "dropped_capture_points": 0, "hits_percents": 0, "draws": 0, "max_xp_tank_id": null, "battles": 0, "damage_received": 0, "frags": 0, "capture_points": 0, "max_damage_tank_id": null, "max_damage": 0, "hits": 0, "battle_avg_xp": 0, "wins": 0, "losses": 0, "damage_dealt": 0, "no_damage_direct_hits_received": 0, "max_frags": 0, "shots": 0, "explosion_hits_received": 0, "tanking_factor": 0 }, "stronghold_skirmish": { "spotted": 30, "max_frags_tank_id": 4929, "max_xp": 958, "direct_hits_received": 101, "explosion_hits": 2, "piercings_received": 90, "piercings": 58, "xp": 7576, "survived_battles": 6, "dropped_capture_points": 3, "hits_percents": 59, "draws": 0, "max_xp_tank_id": 11553, "battles": 27, "damage_received": 21485, "frags": 4, "capture_points": 89, "max_damage_tank_id": 2849, "max_damage": 2381, "hits": 77, "battle_avg_xp": 281, "wins": 9, "losses": 18, "damage_dealt": 15536, "no_damage_direct_hits_received": 11, "max_frags": 1, "shots": 130, "explosion_hits_received": 2, "tanking_factor": 0.06 }, "historical": { "spotted": 0, "avg_damage_assisted_track": 0, "max_xp": 0, "avg_damage_blocked": 0, "direct_hits_received": 0, "explosion_hits": 0, "piercings_received": 0, "piercings": 0, "max_damage_tank_id": null, "xp": 0, "survived_battles": 0, "dropped_capture_points": 0, "hits_percents": 0, "draws": 0, "max_xp_tank_id": null, "battles": 0, "damage_received": 0, "avg_damage_assisted": 0, "max_frags_tank_id": null, "frags": 0, "avg_damage_assisted_radio": 0, "capture_points": 0, "max_damage": 0, "hits": 0, "battle_avg_xp": 0, "wins": 0, "losses": 0, "damage_dealt": 0, "no_damage_direct_hits_received": 0, "max_frags": 0, "shots": 0, "explosion_hits_received": 0, "tanking_factor": 0 }, "team": { "spotted": 69, "avg_damage_assisted_track": 67.39, "max_xp": 1515, "avg_damage_blocked": 0, "direct_hits_received": 260, "explosion_hits": 0, "piercings_received": 203, "piercings": 192, "max_damage_tank_id": 5377, "xp": 45516, "survived_battles": 35, "dropped_capture_points": 158, "hits_percents": 45, "draws": 0, "max_xp_tank_id": 5377, "battles": 74, "damage_received": 30255, "avg_damage_assisted": 110.22, "max_frags_tank_id": 5377, "frags": 27, "avg_damage_assisted_radio": 42.82, "capture_points": 391, "max_damage": 2691, "hits": 327, "battle_avg_xp": 615, "wins": 58, "losses": 16, "damage_dealt": 35223, "no_damage_direct_hits_received": 57, "max_frags": 3, "shots": 726, "explosion_hits_received": 0, "tanking_factor": 0 }, "max_damage_tank_id": 9297, "frags": null, "max_damage": 7782, "max_damage_vehicle": 9297, "max_frags": 8 }, "nickname": "Hyster", "ban_info": null, "logout_at": 1445732977 } } } Quote Link to comment https://forums.phpfreaks.com/topic/298803-database-design/#findComment-1524245 Share on other sites More sharing options...
benanamen Posted October 25, 2015 Share Posted October 25, 2015 (edited) For the most part, it looks like your tables are already set to go. Each of those headers and its data would be a table with an id column in each tied to the main id. I see there is repeating sub header names. Those would be additional tables. What you have would be on the complicated side for a beginner but we can help you. You must really understand Database Normalization with what you have going on. Its not a complicated subject and will really help you get this set up. Edited October 25, 2015 by benanamen 1 Quote Link to comment https://forums.phpfreaks.com/topic/298803-database-design/#findComment-1524250 Share on other sites More sharing options...
hyster Posted October 25, 2015 Author Share Posted October 25, 2015 thanks again benanamen Quote Link to comment https://forums.phpfreaks.com/topic/298803-database-design/#findComment-1524251 Share on other sites More sharing options...
0x00 Posted October 25, 2015 Share Posted October 25, 2015 If you're not using those columns to search and just retrieving a lot of it to pass back for display / process purposes you could save a php array in a text column using json_encode / json_decode? Quote Link to comment https://forums.phpfreaks.com/topic/298803-database-design/#findComment-1524253 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.