CODEWITHSUNDEEP
phpdatabasedatabase-design
Jared
Jared

Reputation: 65

Database with 40000+ records per day

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

Upvotes: 2

Views: 413

Answers (4)

Matthew
Matthew

Reputation: 48284

If you create a summary table that contains the monthly total per user, surely that would be the primary usage of the system, right?

Every month, you crunch the numbers and store the totals into a second table. You can prune the log table on a rolling 12 month period. i.e., The old data can be stuffed in the corner to keep the indexes smaller, since you'll only need to access it when the city is accused of fraud.

So exactly how you store the daily readouts isn't that big of a concern that you need to be freaking out about it. Giving each user his own table is not the proper solution. If you have tons and tons of data, then you might want to consider sharding via something like MongoDB.

Upvotes: 2

Glenn Dayton
Glenn Dayton

Reputation: 1430

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.

Upvotes: 8

Fluffeh
Fluffeh

Reputation: 33512

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.

Upvotes: 5

Mahn
Mahn

Reputation: 16585

Generally speaking tables should represent sets of things. In your example, it's easy to identify the sets you have: users and readouts; there the theoretical best structure would be having those two tables, where the readouts entries have a reference to the id of the user.

MySQL can handle very large amounts of data, so your best bet is to just try the user-readouts structure and see how it performs. Alternatively you may want to look into a document based NoSQL database such as MongoDB or CouchDB, since storing readouts reports as individual documents could be a good choice aswell.

Upvotes: 4

Related Questions