[SupremeCommander]

Hello everyone,

I have a question regarding SQL(or MySQL) database. Is it okay to have huge number of columns for a table(say 200) even after normalising it? I believe I normalised my database and no longer have any redundant nor null values but my tables have high number of fields(much data?). This leads me to a doubt in performance; would it be bad for performing queries as compared to normal file system(Saving/loading)?

Also, is it better to have multiple tables to store data rather than 1? Consider an example with a table `account` which stores all user related stuff. Now lets say I have inventory with 50 items. Is it better to make a new table `inventory` to store the data(multiple tables) or just merge it to `account`(still normalised right?).

[nmader]

MySQL is, in my opinion, the best optimized method to save anything in terms of speed and efficient use of RAM and CPU. As long as you are not constantly saving large strings you should be fine, just set everything on a timer to save only when they need to to back up server data.

In the end it's ultimately going to come down to the same speed regardless (maybe nanoseconds [a billionth of a second]), it's down to personal preference, because you're also going to have to link the two it may be more coding work to separate them or remember they're in two different databases. But in the end you are just selecting from a different table, not loading the both of them as long as you are freeing results appropriately.

[Macluawn]

Quote:

Originally Posted by SupremeCommander I believe I normalised my database Consider an example with a table `account` which stores all user related stuff. Now lets say I have inventory with 50 items. Is it better to make a new table `inventory` to store the data(multiple tables) or just merge it to `account`(still normalised right?).

If you havent done that, your database is not normalized.
But you dont need to follow all 6 normalforms (or howevermany there are). Denormalization is about performance after all, so I wouldnt worry too much about hat.
Just make sure you separate text type fields in a separate table, otherwise sorting is going to be a bitch.

[StreetGT]

Quote:

Originally Posted by nmader MySQL is, in my opinion, the best optimized method to save anything in terms of speed and efficient use of RAM and CPU. As long as you are not constantly saving large strings you should be fine, just set everything on a timer to save only when they need to to back up server data. In the end it's ultimately going to come down to the same speed regardless (maybe nanoseconds [a billionth of a second]), it's down to personal preference, because you're also going to have to link the two it may be more coding work to separate them or remember they're in two different databases. But in the end you are just selecting from a different table, not loading the both of them as long as you are freeing results appropriately.

Would loose if SA-MP had a MongoDB plugin ^^

[SupremeCommander]

Quote:

Originally Posted by nmader MySQL is, in my opinion, the best optimized method to save anything in terms of speed and efficient use of RAM and CPU. As long as you are not constantly saving large strings you should be fine, just set everything on a timer to save only when they need to to back up server data. In the end it's ultimately going to come down to the same speed regardless (maybe nanoseconds [a billionth of a second]), it's down to personal preference, because you're also going to have to link the two it may be more coding work to separate them or remember they're in two different databases. But in the end you are just selecting from a different table, not loading the both of them as long as you are freeing results appropriately.

I see, but what I have in mind is, lets say you have 2 tables and you perform all sorts of joins and crazy stuff to get result, wouldn't that be like an overhead as compared to having a single table(Above example)?

Quote:

Originally Posted by Macluawn If you havent done that, your database is not normalized. But you dont need to follow all 6 normalforms (or howevermany there are). Denormalization is about performance after all, so I wouldnt worry too much about hat. Just make sure you separate text type fields in a separate table, otherwise sorting is going to be a bitch.

How is it not normalised? Could you please explain. I did my normalisation till 3NF.

Quote:

Originally Posted by StreetGT Would loose if SA-MP had a MongoDB plugin ^^

That's something worth learning!

Thanks for replies everyone!

[Macluawn]

If it works as it, dont touch it. Only change the structure if you find it to be a bottleneck (which isn't likely).

Joins and subqueries are the bread and butter of relation databases. Remember to add indexes to your columns.
By not normalized I meant that inventory and account are two completely different entities and should be separated.
If you want to have this data in the same table, look into table views.

Quote:

Originally Posted by StreetGT Would loose if SA-MP had a MongoDB plugin ^^

Obligatory https://www.youtube.com/watch?v=b2F-DItXtZs
If you have relation data, use a relation database. Document databases dont make everything magically fast, you can still fuck up with it the same as with relation databases. Dont underestimate mysql.

[Vince]

Quote:

Originally Posted by SupremeCommander I believe I normalised my database

Somehow I seriously doubt that. I would say the acceptable maximum number of columns for a single table is about 30. Do you have a datamodel that I can take a look at?