1

There are three MySQL-based web applications, that need the same data, e.g. geo data. This data logically is not a part of the apllications' data. And even more than this -- it's external data and normally an independent database.

How should one handle this? Import this data into / Store this data in all the three databases? If not, what strategies are there to share this data between to use this data in multiple databases?

Additional (background) information:

Some time ago I wrote a web application for managing sports courses. This database contains also a very simple table cities with columns id and name and about 20 manually inserted cities in it.

Now, I'm building two another apps (for sports partners search and managing of the ads on the website). Both also need geo info, particularly cities.

But this time I need professional geo data. I choosed the OpenGeoDB.

So, I have to make a decision, how to store/manage the geo data and use this in all the three applications.

automatix
  • 14,018
  • 26
  • 105
  • 230
  • Why wouldn't you have each of your three existing applications connect to a second database - your geo database? – AgRizzo May 19 '14 at 11:21
  • I don't only read the data from it. I also have to build foreign keys. E.g. my `courses` table in the sports courses database contains a column `city_id`, that references entries in the table `cities` and should now reference a column in the geo locations table. But as I know, it's not possible to set constraints between daabases. – automatix May 19 '14 at 11:27
  • 1
    I was wrong, [it *is* possible to set constraints across databases](http://stackoverflow.com/a/3905029/2019043). So, then the solution (at least for MySQL-only infrastructure and on one machine) is pretty simple: [To store data in different databases (or more correctly "schemas") and use it in the same way as in a single database](http://stackoverflow.com/a/23736881/2019043). – automatix May 19 '14 at 12:07

2 Answers2

2

In the MySQL-only context the solution is simple: To store data in different databases (or more correctly "schemas") and use it in the same way as in a single database. Since in MySQL it's possible to build CONSTRAINTs and JOINs across the schemas, just using the comon syntax and adding the schema nam to spacify the table:

FOREIGN KEY

-- Schema schema_a
CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

-- Schema schema_b
CREATE TABLE child (
    id INT, 
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) 
        REFERENCES schema_a.parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

JOIN

SELECT
    *
FROM
    schema_b.child
JOIN
    schema_a.parent ON schema_b.child.parent_id = schema_a.parent.id
;

And it even should not have any porformance disadvantages, as long as all the databases are stored on the same machine.

Community
  • 1
  • 1
automatix
  • 14,018
  • 26
  • 105
  • 230
0

If you need separate MySQL servers for performance reasons, then I would recommend loading the data (via mysqldump or LOAD FROM INFILE) to each of the databases. The reason is that a) you will not overload the single GEO database, and b) you can join to it locally on each MySQL server (as other answers indicate). This should be very fast, and since the GEO data does not change (or not often) this could be very easy to script and manage.

dbschwartz
  • 763
  • 3
  • 10