multiple batabases vs. multiple tables

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View


I am about migrate from an old program database to MySql (Running under RH
LINUX)and I'm wondering which is the best option to do :

I currently have one file for each of my modules, (I'm using filepro plus) ,
so like CUSTOMER, WARRANTY, ZIPCODES, INV, etc... like 2,000 files each with
their own fields.

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 ?


Re: multiple batabases vs. multiple tables

E Arredondo wrote:
Quoted text here. Click to load it

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

- 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.

Bill K.

Site Timeline