Which is better

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Flamie
Forum Contributor
Posts: 166
Joined: Mon Mar 01, 2004 3:19 pm

Which is better

Post by Flamie »

I have a user table that is getting too big (too many fields), and all the information thats in this table needs to be pulled for the game I'm making which would you suggest I do:
-Keep adding to that table
-Make a new table and pull information from both tables everytime
-it doesnt matter

Thanks :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

option 4: post the table structure, and justification and explanation for the fields.
Flamie
Forum Contributor
Posts: 166
Joined: Mon Mar 01, 2004 3:19 pm

Post by Flamie »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[syntax="sql"]CREATE TABLE `rpg_players` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `name` text NOT NULL,
  `race_id` int(11) NOT NULL default '0',
  `map_id` int(11) NOT NULL default '1',
  `battle_id` mediumint(9) NOT NULL default '0',
  `health` int(11) NOT NULL default '100',
  `maxhp` int(11) NOT NULL default '100',
  `status` text NOT NULL,
  `city_id` int(11) NOT NULL default '1',
  `city_screen` tinyint(4) NOT NULL default '1',
  `cemetery` mediumint(9) NOT NULL default '1',
  `inquest` tinyint(4) NOT NULL default '-1',
  `partymembers` varchar(12) NOT NULL,
  `mode` int(11) NOT NULL default '-1',
  `ischat` tinyint(1) NOT NULL default '0',
  `strength` int(11) NOT NULL default '5',
  `defense` int(11) NOT NULL default '5',
  `agility` int(11) NOT NULL default '30',
  `tokens` int(11) NOT NULL default '100',
  `maxtokens` int(11) NOT NULL default '500',
  `money` int(11) NOT NULL default '100000',
  `sherif` mediumint(9) NOT NULL default '0',
  `wins` int(11) NOT NULL default '0',
  `losses` int(11) NOT NULL default '0',
  `battleswon` int(11) NOT NULL,
  `battleslost` int(11) NOT NULL,
  `battlesescaped` int(11) NOT NULL,
  `monsterskilled` int(11) NOT NULL,
  `distancewalked` int(11) NOT NULL,
  `posx` int(11) NOT NULL default '0',
  `posy` int(11) NOT NULL default '0',
  `area` varchar(15) NOT NULL,
  `area_id` int(11) NOT NULL,
  `lastarea` varchar(15) NOT NULL,
  `experience` int(11) NOT NULL,
  `ap` smallint(6) NOT NULL,
  `skills` text NOT NULL,
  `weapons` varchar(255) NOT NULL,
  `quests` text NOT NULL,
  `keyitems` text NOT NULL,
  `mugging` int(11) NOT NULL default '5',
  `aiming` int(11) NOT NULL default '5',
  `throwing` int(11) NOT NULL default '5',
  `luck` int(11) NOT NULL default '5',
  `balance` int(11) NOT NULL default '5',
  `dropping` int(11) NOT NULL default '5',
  `roping` int(11) NOT NULL default '5',
  `spying` tinyint(4) NOT NULL default '5',
  `awareness` tinyint(4) NOT NULL default '5',
  `chests` text NOT NULL,
  `hidenseek` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) 
The reason they're pulled everytime is because 95% of them are used on every page load in one way or another.
Some other fields I want to add are like
music (boolean for off/on)
music_level
sound
sound level
theme
and a few others


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

You should never base a database structure decision on "how many" anything. There are formal rules that determine whether a table is normalized. If all the fields are legitimate properties of the entity represented by the table, they should be in the same table. Period. You will drive yourself wacko if you start making arbitrary structural changes that aren't dictated by the entity and its properties. You won't suffer any performance loss if you structure it in accordance with the rules of normalization.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

Flat tables are easy to modify but makes the table full of redundant data. Suppose, in your case, if user has many settings, you have to repeat the same other information again with little change etc. So, making a relational database will help you to reduce the data redundancy but makes hard to fetch data if you have many tables.
So, good idea is to do the system analysis at first and move toward design of database and coding.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You might also want to look into indexing and optimizing your tables. That will almost add speed and efficiency to your queries.
Post Reply