Database design advice

Databases
1

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.

2

That’s totally fine. Just make sure you have indexes on any columns used in any SQL clauses – join columns, sorting columns, and even columns used in a where clause.

Yes, that sounds good.

No. Definitely, definitely no. Never do something like that. That would make a cluttered mess that is completely unnecessary. Creating an index will get you the same performance gains without creating 500+ tables.

Your schema should probably look something like this.

users (presumably your company's staff)
----------------------
| id | whatever_else |
----------------------

clients
-----------------------------
| id | name | whatever_else |
-----------------------------

client_notes
--------------------------------------
| id | user_id | client_id | content |
--------------------------------------

hours
--------------------------------------------------
| user_id | client_id | reporting_period | hours |
--------------------------------------------------
^                                        ^
|------------------ PK ------------------|
3

I’m not sure what RDBMS you have there, but it looks like some PARTITION BY is in order.

4

why do you need to partition a table with “only” two and a half million records? Properly indexed that isn’t troublesome to a database.

5

A partition can act as a standalone table: smaller set of records, to read/write, own indexes, smaller to load than the big whole table ones. This means faster reads/writes while keeping a unified structure.

2.5m records is big or small depending on the RDBMS and its hosting server capabilities. But usually it means big. I don’t think the OP is corporate.