Database with 40000+ records per day

Question

I am creating a database for keeping track of water usage per person for a city in South Florida.

There are around 40000 users, each one uploading daily readouts.

I was thinking of ways to set up the database and it would seem easier to give each user separate a table. This should ease the download of data because the server will not have to sort through a table with 10's of millions of entries.

Am I false in my logic?
Is there any way to index table names?
Are there any other ways of setting up the DB to both raise the speed and keep the layout simple enough?

-Thank you,
Jared

p.s. The essential data for the readouts are:
-locationID (table name in my idea)
-Reading
-ReadDate
-ReadTime

p.p.s. during this conversation, i uploaded 5k tables and the server froze. ~.O
thanks for your help, ya'll

Answer 1

Setting up thousands of tables in not a good idea. You should maintain one table and put all entries in that table. MySQL can handle a surprisingly large amount of data. The biggest issue that you will encounter is the amount of queries that you can handle at a time, not the size of the database. For instances where you will be handling numbers use int with attribute unsigned, and instances where you will be handling text use varchar of appropriate size (unless text is large use text).

Handling users If you need to identify records with users, setup another table that might look something like this:

• user_id INT(10) AUTO_INCREMENT UNSIGNED PRIMARY
• name VARCHAR(100) NOT NULL

When you need to link a record to the user, just reference the user's user_id. For the record information I would setup the SQL something like:

• id INT(10) AUTO_INCREMENT UNSIGNED PRIMARY
• u_id INT(10) UNSIGNED
• reading Im not sure what your reading looks like. If it's a number use INT if its text use VARCHAR
• read_time TIMESTAMP

You can also consolidate the date and time of the reading to a TIMESTAMP.

Answer 2

Do NOT create a seperate table for each user.

Keep indexes on the columns that identify a user and any other common contraints such as date.

Think about how you want to query the data at the end. How on earth would you sum up the data from ALL users for a single day?

If you are worried about primary key, I would suggest keeping a LocationID, Date composite key.

Edit: Lastly, (and I do mean this in a nice way) but if you are asking these sorts of questions about database design, are you sure that you are qualified for this project? It seems like you might be in over your head. Sometimes it is better to know your limitations and let a project pass by, rather than implement it in a way that creates too much work for you and folks aren't satisfied with the results. Again, I am not saying don't, I am just saying have you asked yourself if you can do this to the level they are expecting. It seems like a large amount of users constantly using it. I guess I am saying that learning certain things while at the same time delivering a project to thousands of users may be an exceptionally high pressure environment.