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'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.

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?