...

View Full Version : How many is too many fields?



myfayt
06-24-2011, 01:12 PM
If I had 500 fields in a table of my database, would that lag and affect anything rather than having say 75 fields?

Because the user field is the main table and has roughly 250 fields in it, and I'd like to add all items to it, so that I won't have to merge tables to calculate.

oracleguy
06-24-2011, 04:19 PM
Well I'll defer to the experts on the performance issues with that many fields but I can tell you that from a usability and maintenance point of view that is too many fields in one table. Why do you have so many? Typically the reason a table has that many is people create fields like somechoice_1, somechoice_2, somechoice_3 instead of breaking that out into another table with a one to many relationship.

bazz
06-24-2011, 06:29 PM
Because the user field is the main table and has roughly 250 fields in it, and I'd like to add all items to it, so that I won't have to merge tables to calculate.

Very bad idea!! Please read up on NORMALISATION and REFERENTIAL INTEGRITY.

Storing all in one table, will make indexing for certain queries more difficult not to mention that management of relationships will be pretty much impossible. And the amount of duplicated data will be more inefficient than joining tables.

bazz

myfayt
06-24-2011, 08:51 PM
Yeah I thought it was a bad idea. I don't know the knowledge or experience to do normalizing. I have lots of items so I was going to do it like this.

USER TABLE
drink (0)
food (0)
clothes (0)
wood (0)

etc. Those aren't the items but it's an example, so when they purchase something on the game it adds to that fields.
I thought it'd be a nightmare to do it by type, like if type1 is healing, then all healing would go under there.

I want to list everything they have in an inventory.

So like

Blue Herb: 15
Red Herb: 123
Yellow Herb: 9
Orange Herb: 1,038

etc.

Old Pedant
06-24-2011, 09:03 PM
TABLE: Users
userid INT PRIMARY KEY
username VARCHAR
...other fields that describe the *USER* ...

TABLE: Objects
objectType INT AUTO_INCREMENT PRIMARY KEY
objectName VARCHAR
objectProperties ???

TABLE: UserObjects
userid INT REFERENCES Users(userid)
objectType INT REFERENCES Object(objectType)
quantity INT
strength INT (if appropriate)


Or something along those lines.

It's called a "many-to-many" table. Very very standard in normalized databases.

myfayt
06-25-2011, 01:50 AM
Hmm I don't quite understand the UserObjects table. Are you merging the two together?

Old Pedant
06-25-2011, 03:36 AM
Example of data in all tables:


Users
1 : Homer
2 : Marge

Objects:
1 : BlueHerb : Healing
2 : OrangeHerb : Healing

UserObjects
1 : 1 : 15 (Homer has 15 Healing BlueHerbs)
1 : 2 : 1038 (Homer has 1038 Healing OrangeHerbs)
2 : 2 : 771 (Marge has 771 Healing OrangeHerbs)


I'd suggest that the third column in Objects could be an ENUM column *OR* it could be an INT that is a foreign key to a separate ObjectTypes table.

bullant
06-25-2011, 04:03 AM
If I had 500 fields in a table of my database, would that lag and affect anything rather than having say 75 fields?

Because the user field is the main table and has roughly 250 fields in it, and I'd like to add all items to it, so that I won't have to merge tables to calculate.

500 fields :eek: in a table doesn't sound like a good idea to me either. With that many fields it looks like you're going to need a few tables.

I think you should first read up on database normalization (http://databases.about.com/od/specificproducts/a/normalization.htm) which should help you organise the data you want to store in the database.

An extract from the above link:


What is Normalization?

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Then draw up an ERD (Entity Relationship Diagram (http://www.google.com.au/imgres?imgurl=http://www.kirupa.com/developer/php/images/Portfolio_Database_ERD.png&imgrefurl=http://www.kirupa.com/developer/php/relational_db_design8.htm&h=420&w=450&sz=108&tbnid=2Um3wlVyxHj-gM:&tbnh=119&tbnw=127&prev=/search%3Fq%3Ddatabase%2Bentity%2Brelationship%2Bdiagram%26tbm%3Disch%26tbo%3Du&zoom=1&q=database+entity+relationship+diagram&usg=__zZNScofGQkJsdVAackN7ECaEF-c=&sa=X&ei=ZU8FTrrmHqXFmAW5uNXHDQ&ved=0CCQQ9QEwAw)) which is a schematic representation of your database.

Then build the sql scripts to create the database and the tables within it.

myfayt
06-25-2011, 12:46 PM
Example of data in all tables:


Users
1 : Homer
2 : Marge

Objects:
1 : BlueHerb : Healing
2 : OrangeHerb : Healing

UserObjects
1 : 1 : 15 (Homer has 15 Healing BlueHerbs)
1 : 2 : 1038 (Homer has 1038 Healing OrangeHerbs)
2 : 2 : 771 (Marge has 771 Healing OrangeHerbs)


I'd suggest that the third column in Objects could be an ENUM column *OR* it could be an INT that is a foreign key to a separate ObjectTypes table.

Ah I think I am getting it. So you're comparing two like values. Like say you have 'playerid' you put that in Users and Objects table, both would have the same value. Then in the UserObjects table, you'd find a match on playerid, and then load the fields for that player. Am I getting closer?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum