I'm fairly new to creating my own MySQL databases, and my first major mysql project is an online encyclopedia of characters, planets, races, and such for my game (may be published soon!). I'm also integrating a character creation system into this where when a character is created by a user, it shows up instantly.

I'm planning on having many records in this database that have to store this (http://www.2thextreme.org/LAF.html) much info and more. So far I have 105 fields and I still need to add an additional 150 or so. I need a little help with this part. currently one record takes up about 8KB and I plan on at least 200+ records in this table.

I need to add all the skill fields, but should I store them in a separate table and link them to the character by ID number, or should I add the skill fields to the same table as the character's information? I'm planning on making the database searchable by first and last name (for regular users) and username and password (for admins).

The ones I'm not sure about are the decimal(4,2) ones. if I have it set as (4,2) will the number be entered and read like this ##.##? and if it is (5,3) like this ##.###? I'm just wanting to make sure I have the database in good shape before I start adding the other info.

Here is my table structure. Does it look ok? I've read the manuals and stuff, I'm just making sure I *understand* what I read ;)


CREATE TABLE npcs (
id int(11) NOT NULL auto_increment,
user varchar(32) NOT NULL default '',
pw varchar(32) NOT NULL default '',
realfirst varchar(20) NOT NULL default '',
reallast varchar(30) NOT NULL default '',
bnamefirst varchar(20) NOT NULL default '',
bnamelast varchar(32) NOT NULL default '',
warname varchar(24) NOT NULL default '',
codeprefix varchar(7) NOT NULL default '',
codesuffix char(3) NOT NULL default '',
civilization varchar(55) NOT NULL default '',
birthday varchar(10) NOT NULL default '',
birthmonth varchar(9) NOT NULL default '',
birthdate int(2) unsigned default NULL,
birthyear int(4) unsigned default NULL,
gender varchar(13) NOT NULL default '',
iq decimal(4,2) default NULL,
csq decimal(4,2) default NULL,
body varchar(20) NOT NULL default '',
status varchar(40) NOT NULL default '',
height decimal(4,2) default NULL,
weight decimal(4,2) default NULL,
bust varchar(5) default NULL,
waist varchar(5) NOT NULL default '',
hips varchar(5) NOT NULL default '',
dress varchar(4) NOT NULL default '',
skintype varchar(15) NOT NULL default '',
skincolor varchar(32) NOT NULL default '',
hairtype varchar(15) NOT NULL default '',
haircolor varchar(32) NOT NULL default '',
hairlength varchar(15) NOT NULL default '',
hairtexture varchar(10) NOT NULL default '',
hairstyle varchar(20) NOT NULL default '',
eyetype varchar(15) NOT NULL default '',
eyecolor varchar(10) NOT NULL default '',
nosetype varchar(15) NOT NULL default '',
nosesize varchar(10) NOT NULL default '',
eartype varchar(15) NOT NULL default '',
earsize varchar(10) NOT NULL default '',
mouthtype varchar(15) NOT NULL default '',
mouthsize varchar(10) NOT NULL default '',
teethtype varchar(15) NOT NULL default '',
teethcolor varchar(10) NOT NULL default '',
teethsize varchar(10) NOT NULL default '',
liptype varchar(15) NOT NULL default '',
lipcolor varchar(10) NOT NULL default '',
lipsize varchar(10) NOT NULL default '',
fingertype varchar(15) NOT NULL default '',
fingersize varchar(10) NOT NULL default '',
fingerclawtype varchar(15) NOT NULL default '',
fingerclawblade varchar(25) NOT NULL default '',
fingerclawcolor varchar(15) NOT NULL default '',
fingerclawmin decimal(4,2) default NULL,
fingerclawmax decimal(4,2) default NULL,
foottype varchar(15) NOT NULL default '',
footsize int(2) unsigned default NULL,
footclawtype varchar(15) NOT NULL default '',
footclawblade varchar(25) NOT NULL default '',
footclawcolor varchar(15) NOT NULL default '',
footclawmin decimal(4,2) default NULL,
footclawmax decimal(4,2) default NULL,
sstype varchar(15) NOT NULL default '',
sscolor varchar(15) NOT NULL default '',
ssspanmin decimal(4,2) default NULL,
ssspanmax decimal(5,2) default NULL,
clothingdesc text NOT NULL,
earrings text NOT NULL,
necklaces text NOT NULL,
rings text NOT NULL,
bracelets text NOT NULL,
belts text NOT NULL,
anklets text NOT NULL,
other text NOT NULL,
offense int(2) unsigned default NULL,
defense int(2) unsigned default NULL,
mental int(2) unsigned default NULL,
knowledge int(2) unsigned default NULL,
strength int(2) unsigned default NULL,
agility int(2) unsigned default NULL,
speed int(2) unsigned default NULL,
strategy int(2) unsigned default NULL,
magic int(2) unsigned default NULL,
dexterity int(2) unsigned default NULL,
vitality int(2) unsigned default NULL,
power decimal(5,3) default NULL,
earth varchar(12) NOT NULL default '',
wind varchar(12) NOT NULL default '',
fire varchar(12) NOT NULL default '',
water varchar(12) NOT NULL default '',
love varchar(12) NOT NULL default '',
life varchar(12) NOT NULL default '',
death varchar(12) NOT NULL default '',
hate varchar(12) NOT NULL default '',
time varchar(12) NOT NULL default '',
space varchar(12) NOT NULL default '',
personal text NOT NULL,
family text NOT NULL,
mentalskills text NOT NULL,
physicalskills text NOT NULL,
notes text NOT NULL,
skillset varchar(32) default NULL,
spellset varchar(32) default NULL,
version tinyint(3) unsigned default NULL,
chartype char(3) default NULL,
ccid varchar(10) default NULL,
PRIMARY KEY (id,user,pw,reallast,realfirst)
) TYPE=MyISAM;


Thanks in advance :)

Did you read about normalizing your database?

This is fine as long as all these fields are meant just for this one record.

In other words, you shouldn't have repeating data with in your database.

If a client/customer enters his/her info and that has to be referenced to other tables you should just place the ID field in the other tables to have a cross-reference to the client/customer table.

Relational Databases are great!!!

As far as your decimal question below is the answer:

decimal (8,2) = ######.##
decimal (2,2) = ##.##
decimal (2,3) = ##.###
and so on

Oh, for the record, I'm building the script with php 4.2.2, and MySQL 3.23.47 on Windows.

Normalizing the database? Nope, I hevent read about that... Looks like its time for me to google around :)

Hmm... Maybe I'm being completely dense, or I need to slave over the MySQL manual again. (The mysql manual stinks for newbies :( ) I dont understand what you mean, so talk to me like I'm stupid :eek: if you dont mind :):


This is fine as long as all these fields are meant just for this one record.

In other words, you shouldn't have repeating data with in your database.

If a client/customer enters his/her info and that has to be referenced to other tables you should just place the ID field in the other tables to have a cross-reference to the client/customer table.


Thanks again.

If you have 2 tables that store information about 1 item:

Example:
table 1 stores customer data (name, address etc)

Table 2 stores items they purchased

Instead of listing the customers name and all that jazz in table 2, you just reference the customer by a customer id assigned in table 1.

So jane doe would be customer 18 and the customer id in table 2 would relate the information back to table 1 to pull up the customer name (jane doe)

Thanks goodness0001, thats what I intended to do but I didnt think I made myself clear. :homer:

Looking at my table structure, I plan on doing this twice. Once for "skills", and the other for "spells" (skillset, spellset respectively). Neither of these will be called all the time and thus I wanted to put them in different tables. How efficient is this compared to putting everything in one table?

Anyways, another question. how efficent is an "Abstraction Layer"? I've messed with the one included in phpnuke (and thinking about using it), and I found it pretty easy to do queries. What are the pros and cons to using them.

As you've notcied I'm looking for simple, yet efficient ways of doing things since I believe this program will be pretty complex and will store quite a bit of information (I have over 400 pages of stuff I'll be adding to it :eek: ).