Database design question

[johnsmith153]

I am adding to a system that stores details about work completed for their clients.

 

There's a lot going on and there's around 60 database tables for various things which have already been developed in one database.

 

I now need to add tables for notes made by their staff about a client. The way it works is that about 20 or so notes can be added per week per client. They want to keep this for 5 years and with about 500 clients I work this out at a maximum need of about 2,600,000 records (20 a week * 52 weeks * 5 years * 500 clients).

 

There isn't really any way round the number of notes. They have people add these notes on their mobiles / computers at various points throughout the day apparently.

 

Also, similar to notes, they need to log number of hours worked and again this can involve about 10 entries per week as different people log hours worked at different times. So, this could easily be well over 1 million records.

 

I'm thinking it might be best to have one notes table and one hours table per client. That way the notes table would be 500 times smaller at about 5,000 maximum records.

 

There won't be any need to produce reports for all of the notes or hours at one time, so a separate table makes sense. It's not as though we'd ever need to join all 500 client tables to produce reports as it won't be needed.

 

As for creating a new table per client then a PHP script would add a new table when they 'add a client' on their back-end system, so it would all be automated.

 

So the questions are

 

(1) Should I have one notes table for all (2.6m records) or one per client?

 

(2) Should I have one hours worked table (over 1m records) for all or one per client?

 

(3) Should I create all this in the one database or use a 2nd and/or 3rd database for the extra tables?

 

If I create it all in one database then would it be odd that the existing 60 or so tables are in with probably 1,000 other tables that are automatically generated (and probably deleted if a client leaves), or would having 3 databases be the wrong way to go about it?

 

Thanks.

[Mahngiel]

I think it would be much more inefficient to have a table for each client.  What you could do perhaps is to split the tables into alphabetical chunks (A-F, G-L, M-O, R-T, U-Z).

[Pikachu2000]

2.6 M records is nothing.

 

Dynamically adding/dropping tables is often a sign of a serious design flaw.