E Arredondo wrote:
1) Should I create multiple databases on MySQL
2) Should I create a single database on MySql and with multiple tables ?
Which one of these options is the best and more safer ?
Creating each table in a separate database has at least a couple of
disadvantages that I can think of right away:
- You can't do operations involving multiple tables easily in SQL, if
the tables are in separate databases. That is, a JOIN, or an INSERT to
one table based on values from another table, etc. It's best to keep
together at least tables that you might use together in a single SQL
statement.
- Backups would need to be carefully managed, because you could easily
back up one database at a different time than another database, and data
could be changed in the time in between. So values in one table might
not match up to values in the other table. It's better to keep tables
that are related together; that is, if it's important to have
referential integrity between these tables, put them together in the
same database.
My practice is to keep all tables related to a given project together in
one database, even if they don't hit the two issues listed above. It's
just simpler that way.
Regards,
Bill K.