CODEWITHSUNDEEP
mysqldatabaserelational-databasedata-storagedata-sharing
automatix
automatix

Reputation: 14542

Does it make sense / How to share data between (MySQL) databases?

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.

Upvotes: 1

Views: 1143

Answers (2)

dbschwartz
dbschwartz

Reputation: 783

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.

Upvotes: 0

automatix
automatix

Reputation: 14542

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.

Upvotes: 2

Related Questions