Page 1 of 1
Which is better
Posted: Sat Dec 02, 2006 12:34 am
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

Posted: Sat Dec 02, 2006 12:52 am
by feyd
option 4: post the table structure, and justification and explanation for the fields.
Posted: Sat Dec 02, 2006 1:02 am
by Flamie
feyd | Please use 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
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]
Posted: Sat Dec 02, 2006 7:01 pm
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.
Posted: Sun Dec 03, 2006 12:10 am
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.
Posted: Sun Dec 03, 2006 1:22 am
by RobertGonzalez
You might also want to look into indexing and optimizing your tables. That will almost add speed and efficiency to your queries.