a database of databases???

Hello all,
I have a small problem thatI hope someone has some thoughts on how to fix or attack. I am new at mysql and there are still a few things I do not know how to do yet. We have a sales force in various locations accross the southeast. All sales guys log into a web server at the end of the day to get account related information. I created a mysql db for one of the sales guys so he could put his account info and other vital contact in and be able to access it anywhere. Well it worked great!!!!!Now the problem is all the other sales guys want the same thing. This is my problem,I do not thinkthat I should have to create 39 mysql databases to solve this problem. Is there a way to somehow give each sales guy a database withinthe current database??? OK I know it sounds crazy. could I create tables for each guy within a single database(these would be huge table...performance would be bad to say the least). How do I fix this...Any ideas would be appreciated. Everything is running on unix/apache.

Thanks

I would put it all in one table...and all the sales people info in another table....

Sales People Table
-------------------
salesperson_id
last_name
first_name
...any other info

Account Info Table
------------------
acct_no
salesperson_id
...other account info


A query such as:

SELECT * FROM account_info where salesperson_id = 12

is very quick no matter how many records are in the database...even millions...with an index on the salesperson_id column.

MySQL can handle hundreds of thousands and millions of rows. Just index properly and you will be fine....

I sort of agree with Matt, you can easily do this with one database but it might not be as simple as two tables. You want to "normalize" your data and not make replications that lead to data anomalies. You should at a minimum have 3-4 tables for this kind of database.

I would reccomend the following 4 tables

salesperson (this has your sales force and all the information you need that is related to the employee)
client (the company, address etc. any info specific to the client)
contact (people at the client company - phone, e-mail etc.)
order (products, salespersonid, clientid, contactid, comments and special instructions)

you might also want to add a table with your products.

By normalizing your data you tighten your database considerably and prevent insertion and deletion anomalies. If you don't understand normalization and data anomalies I am pretty sure you could find several tutorials on google.

You might ask why not combine the contact info with the company info but in reality some companies will have 1 point of contact and others will have a dozen or more, no sense putting 5-10 fields for each possible contact in the same row on the client table and then having lots of blank fields for the smaller companies. Lots of blank fields in your data is often a sign of poor database design.

oops. One more suggestion. You probably don't want to add a saleperson id to the client/account table. It's far better database design to have the link between the clients and the salesperson be in the orders table (this table will be the largest in 99.9% of situations and has few fields) if you must assign a salesperson to each client it is better to add one more table "assignment" so that when new people come on board or assignments change, you don't have the potential for a deletion anomaly and the subsequent loss of historical data. This also allows for multiple sales people to be assigned to a large client and also will accurately reflect who get's the commission if someone was covering for a sick or vacationing employee.