mysql database structure - data only relevant per user thus user tables?

I would like to build an online logbook for truck drivers. The goal is that after a truck driver logs in, he/she immediately sees a snapshot of his/her driving total this year/month/day, together with some other totals also per year/month/day. So the information stored in the database is only relevant per user (truck driver). I personally don't require any statistical data out of the database as a whole (only per user).

Let's assume 10,000 users.

My question relates to the design of the mySQL database.

Since the information stored is only relevant per user and not in mass, does it makes sense to store the data in a table per user... leading up to 10,000 tables? Would that result in the most efficient/fastest database? OR should I dump all rows in one big 'Log' table, and have it relate to another table 'Users'.... even if analysis will only be done per user?

Here's some of the information that needs to be stored per user (ends up to ab开发者_开发知识库out 30 columns): Date - Truck make/model - Truck ID - Route # - From - To - Total time - Stops - Gas consumption - Night time - Crew (2nd driver) - ......

Simplified example here

User
user_id
first_name
last_name

Truck
truck_id
truck_make
truck_model

Route
route_id
user_id
truck_id
route_from
route_to
gas_consumption

Without anymore details to go on this is how I'd roll it.

I would suggest going for separate tables, but maybe in your case 1 large table is a good plan.

Assuming you write efficient MySQL to access the data you shouldn't have a problem with a large dataset such as the one you have described.

I'd take a look at MySQL / Rails Performance: One table, many rows vs. many tables, less rows? for more information on why going for the tables root may be a good idea. Also Which is more efficient: Multiple MySQL tables or one large table? contains some useful information on the subject.

You're describing a multi-tenant database. SO has a tag for that; I added it for you.

MSDN has a decent article giving you an overview of the issues involved in multi-tenant databases. The structures range from shared nothing to shared everything. Note carefully that in a "shared everything" structure, it's fairly easy for the database owner (probably you) to write a query that breaks user isolation and exposes one user's data to other users.