1

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 about 30 columns): Date - Truck make/model - Truck ID - Route # - From - To - Total time - Stops - Gas consumption - Night time - Crew (2nd driver) - ......

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
Katie
  • 11
  • 2
  • I'd go for the 'one table to rule them all' and have a users table that enables you to identify truckers and their logs. With a well defined schema and good queries you shouldnt have any performance issues :) --- edit You may also want to have a trucks table. Otherwise you'll end up with loads of duplicate truck data in your 'logs' table – martynthewolf Jul 11 '11 at 15:39
  • see my answer, might help you out a little more. – martynthewolf Jul 11 '11 at 15:44
  • 2
    Rule of a thumb: As far as relational databases go 'a table per user' is almost never a good idea. I write 'almost' because there might be some extreme cases, but I've yet to hear about them. – Mchl Jul 11 '11 at 15:45
  • I read this as well. Thx for the input! – Katie Jul 11 '11 at 15:48

3 Answers3

1

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.

martynthewolf
  • 1,718
  • 2
  • 11
  • 22
1

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.

Community
  • 1
  • 1
Tom Walters
  • 15,366
  • 7
  • 57
  • 74
  • No problem, I see this is your first question - welcome to SO! Have you considered accepting the answer that best answers your question? – Tom Walters Jul 11 '11 at 15:55
  • @ Tom I'm inclined to go with the dimensional approach: 1 log table related to other dimensions through foreign keys in InnoDB tables. I never had to build a database where the mass data is not relevant for analysis... Personally I don't like the idea of too many tables in a db. – Katie Jul 11 '11 at 16:15
  • I totally agree, having read up on efficiency - too many tables just seems cluttered. – Tom Walters Jul 11 '11 at 16:17
0

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Catcall thank you much for that. I will read the article! The user isolation is certainly one of my worries together with the growth of the log table. Great help, thanks again! – Katie Jul 11 '11 at 16:52
  • Don't overlook restoring from backup. With a "shared everything" structure, restoring one user's data from backup means restoring a few rows in every table. As a practical matter, that might mean you don't support restoring from backup. – Mike Sherrill 'Cat Recall' Jul 11 '11 at 17:11
  • interesting thought. thanks. It seems that a non-shared structure might lead to more hardware requirements... after some initial reading about this concept. – Katie Jul 11 '11 at 17:16