2 databases, vs 1 ?!?

Question

I have a database of all countries and cities, I would like to use ajax to use it to give suggesstions in the country cities fields in forms, I have a lot of tables in my db related to the inner workings of the site. Is it ok if I put this in a different database (just this world map database)? I rather put it in a different db so it's more organized and I wouldnt really be connecting to it on the same pages that I connect to the main database. basically a small php page that will suggest strings with json. I was wondering if a seperate db means sharing the db pooll and decrease of performance!??

I should add that these new tables are and sql script on geobytes.com and I don't know how good they are or if I am gonna use them down the path! I hope to find something better with a uptodate list of ip tables! like ip2location

Answer 1

If you ever need to scale out, your app would already have them split, so it would be easier. You could put the second database on a second machine if it becomes a performance issue.

If you have a many core database server, having them on a single machine shouldn't be a problem for most apps.

Answer 2

Avoid premature optimization. IF you're trying to keep things separate (for architectural reasons), then that's fine. If you're trying to increase performance, then how do you know that this will even increase performance at all? You'll have the overhead of additional data files, possibly with more overhead on your database server to manage multiple databases.

At best it's probably a performance wash.

Answer 3

I don't quite understand why you'd want to do this. It complicates things in terms of:

1. managing 2 databases - e.g. backups and restores.
2. managing 2 connection configurations + connection pools.
3. future potential referential integrity (e.g. at some stage will you have entities tied to cities/countries?)

I would leave these tables where they are for the moment. Pull them out if/when they cause you a performance problem.

Answer 4

There's nothing particularly wrong with that. I dont know if its necessary - if its just a table or two I cant see why it'd matter much performance or security wise, either way. If you are concerned about the script accessing those additional tables, you can use a different database user name for your json script and set that to read only so that if it does get exploited somehow, it cant alter your other tables.

Honestly I think your best bet is to keep it in one db for now, but write your script flexible enough that you can point it at a separate database should the need ever arise.

Answer 5

Sounds like this geographical reference data is your own, whereas your website has a lot of other 'inner workings'.

Is your website a CMS or similar from a 3rd party - Drupal/Joomla/etc?

If so, keep your own data out of there. If you're planning on exposing this geographical data to a web service, it stands by itself.

If this site is your own, consider how much normalization is between the other tables in that database to your geographic reference tables.