considering the performance nd security of MySQL (and other relevant
factors): Is it better to choose a "classroom" approach to structuring a
database based application, or a "quick and dirty" approach?
Class Room Approach
Multiple databases that each contain several related tables.
Quick And Dirty
Create one database and put all tables in that database.
The data being managed consists of:
Headquarters of the organization (approximately 10 - 20 tables. Probably
less than 100'000 records per table).
Several departments (including overseas and regional facilities several
hundred departments, about 10-20 tables per department, about 10'000
records per table).
Sales Reps (Several hundred sales reps per department, 10-20 tables per
sales rep, probably about 1'00 - 1'000 records to be managed per sales
rep per table, in very exceptional cases records can go up to 10'000).
Using the "Class Room" approach I would create a database for each one
of the organizational units headquarters, departments, sales reps. Using
the quick and dirty approach I'd create one database and have different
sets of tables for each organizational unit. I.e. headquarters tables
would be named hq_xxxx where xxx is the table name; departments would be
named dp_<depname>_xxxx, and sales reps tables would be named
In your experience, which approach is more suitable? Is there a
performance penalty (other penalties) when you have many tables in a
database (i.e. queries, joins, ....). Other considerations?