Topic: DB Layout for multiple clients

I starting development of an app to aid farmers in tracking data related to irrigation and pesticide management.  I plan to set up a mySQL database to store the data and am wondering what the best way to set this up for multiple users would be.

My first thought is to have a foreign key column in each table that refers to the farmer whose data is held in that row.  But I'm wondering if this will slow things down when 100+ different farmers are using this database for 100+ entries each per season.

I then had the thought that each farmer could have his own set of tables in the DB titled something like "userName_tableName" but this seems like it would cause lots of headaches and couldn't be set up to automatically create/access these new tables. 

Any suggestions?

Last edited by LeeQ (2007-10-29 14:35:46)

Re: DB Layout for multiple clients

Go with the first approach. It is much more flexible and there are various ways to improve performance (indexing, cacching, etc.) if you need it.

In short, creating a new database for each user will cause a lot of problems in the future.

Railscasts - Free Ruby on Rails Screencasts