Just a quick question guys,

 

PHP and MYSQL

 

Im developing a Facebook Game that runs off a MYSQL database - However, I need to store quitew alot of information for each user such as how much money they have, all the features tehy have in there complex etc. I wqas thinking that creating another table called 'users' for example would be too heavily dependant and would literalty have hundreds of columns as there is alot of information - do you guys have any ideas of some way of creating like a spererate mini database for each user?

 

Im kind of tryign to figure out how gamres such as Mafia Wars remeber so much information about you?

 

 

Thanks In Advance

You certainly don't want to create a separate DB for each user!

What happens if you get say 1000 users (each with their own DB) and then you need to alter the schema? You'd have to do it 1000 times.

 

Try and structure the data properly in different tables and use Foreign Keys to reference them.

 

For example:-

 

USERS Table has a Primary Key column for UserId, then columns for Name, Money etc.

FEATURES Table has a Primary Key column for FeatureId, then columns for FeatureName etc.

USERS_FEATURES table has two columns: UserId and FeatureId. These are Foreign Keys to the respective ids in the USERS and FEATURES tables.

 

You can then write a piece of SQL like this:

SELECT f.FeatureName FROM Features f, Users_Features uf WHERE  f.FeatureId=uf.FeatureId AND uf.UserId=1

 

This would then return a list of Features that User 1 has signed up to.

 

This link may help.

Yeah I agree, a single table or DB per user is a very bad idea.

Although lag is an issue, don't worry about it, every single game on the web has one DB for it's users, it learns to deal with it.

It is all about database management. With such massive amounts of data you will need to make sure you have a fully normalised database so as to be as easy to maintain as possible. If you haven't looked into normalisation before have a look through google, its about removing duplicate text where possible and replacing it with numbers that are foreign keys to other tables. Thats the basic idea anyway.

 

It would also be good to look into triggers and stored procedures that will improve the efficiency of the database.

 

MySQL is pretty robust and as long as you structure your statements well you should be able to get the most out of it. Just make sure you get the most from it in one hit.

Your database would be huge, so you would need to make sure you optimize it pretty well.

 

Eg, don't have multiple of the same data, and only store the user name / personal details once.