MySql: One Table, Multiple Databases

justindublin

Hey Guys,

Hoping someone can help us here. Up front I know very little about SQL, its not my area so forgive me if I seem a little noob.

I'm running MySQL with multiple databases.

Each the databases has one table inside which needs a new entry every day or so, but at the moment I have to manually do this to every single database.

I would love to have just one table that I need to update and all the databases will read from that.

Problem is, I don't really know where to start! I've looked at a few guides online (linking tables) but they all loose me.

Thanks so much in advanced!

seamus

Seems like an odd setup.

Assuming that you can't change the applications using these databases to all look at the same table, then there are a couple of solutions.

You can use an AFTER INSERT trigger on one of the tables to go through the rest of the tables and insert the data.

You could also write a small script in your favourite language which prompts you for the various values to insert and then plops them into each table.

jmcc

Probably a very messy way of doing things:
insert into tablename(whatever) select whatever from otherdatabase.tablename

It is a messy way of doing it but as long as you have read privs on the other database and its tables it is possible.

Regards...jmcc

justindublin

Sorry, I'll explain why i'm doing it.

Its a music program and the track list is stored on one of these tables.

I've say 20 of these audio programs running 20 different databases, and If I want to upload a new song (new entry to the table) it means having to manually add the new entry to each database table one by one.

I figure this way would mean I could upload the song to just one of the audio programs and the rest would pick up the new entry.

seamus

Using Views then are the way to do it, but you need MySQL 5.0 or higher.

A view is basically a virtual table. It acts like a table for all intents and purposes, but it's based on an underlying physical table. When you make changes to the underlying physical table, the view is updated too.

Views are updateable too, so when you make a change to a view, the underlying physical table(s) are updated if the right information is present.

They're also used in the same way, i.e. SELECT * FROM <viewname>. Your application doesn't need to know that it's a view and not a physical table.

What you need to do is designate one table as the "master" table. This could be a table in a different database or one of the existing tables.

Let's say it's a table called "track" and the master table is going to be in "db1".

For all the other tables you will need to drop the existing "track" table and create a view of that name, so;

DROP TABLE db2.track;
CREATE VIEW db2.track AS (SELECT * FROM db1.track);

DROP TABLE db3.track;
CREATE VIEW db3.track AS (SELECT * FROM db1.track);

And so on. Your only problem is that if other tables get updated when you add a track, then you will start to get corruption as the application has difficulty reconciling the two. This can be fixed by turning those tables into views too, but if your current process of updating the databases directly works fine, then this solution should work for you.

justindublin

Couldnt get this to work:
insert into tablename select * from otherdatabase.tablename

I'll clear things up a little.

The main database I wanna copy from is called SamDB
The table I wanna copy from is called called songlist

The empty database is called SamDB2
The empty table is called songlist

So I'd need something to:

Delete everything in SamDB2.songlist
The copy everything from SamDB.songlist into SAMDB2.songlist

Does that make sense?

justindublin

seamus wrote: »

Using Views then are the way to do it, but you need MySQL 5.0 or higher.

A view is basically a virtual table. It acts like a table for all intents and purposes, but it's based on an underlying physical table. When you make changes to the underlying physical table, the view is updated too.

Views are updateable too, so when you make a change to a view, the underlying physical table(s) are updated if the right information is present.

They're also used in the same way, i.e. SELECT * FROM <viewname>. Your application doesn't need to know that it's a view and not a physical table.

What you need to do is designate one table as the "master" table. This could be a table in a different database or one of the existing tables.

Let's say it's a table called "track" and the master table is going to be in "db1".

For all the other tables you will need to drop the existing "track" table and create a view of that name, so;

DROP TABLE db2.track;
CREATE VIEW db2.track AS (SELECT * FROM db1.track);

DROP TABLE db3.track;
CREATE VIEW db3.track AS (SELECT * FROM db1.track);

And so on. Your only problem is that if other tables get updated when you add a track, then you will start to get corruption as the application has difficulty reconciling the two. This can be fixed by turning those tables into views too, but if your current process of updating the databases directly works fine, then this solution should work for you.

I'll give this a bash now...

justindublin

Close but no cigar...

The command completes with no errors:
DROP TABLE samdb2.songlist;
CREATE VIEW samdb2.songlist AS (SELECT * FROM samdb.songlist);

But when I try open the new table I get this:
Error: `samdb2`.`songlist`: table data is not editable because there is no primary key defined for the table

The actual audio program itself cant even create its only song list because of that error.

This VIEW seems to be exactly what I'm after though....

Giblet

Why do you need a database per user? Surely a userId could handle the different users and you could use one table?

seamus

You'll need to define a primary key on samdb.songlist.

This is usually a single column which must be unique in the database.

Just add one in:

ALTER TABLE samdb.songlist ADD COLUMN `song_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

justindublin

Giblet wrote: »

Why do you need a database per user? Surely a userId could handle the different users and you could use one table?

Nah theres another few tables that need to be unique to instance of the program. Settings, Layout etc.

Seamus Massive thank you. I just got the VIEW to work

So am I right in saying that by using:
DROP TABLE db2.songlist;
CREATE VIEW db2.songlist AS (SELECT * FROM db1.songlist);

db2.songlist is just a mirror of db1.songlist

What happens if the program every tries to make a entry to the VIEW (db2) table?

seamus

It will make an entry to the underlying table in db1. This means that there is only one table, any changes made are automatically "propagated" to all databases.

amen

Nah theres another few tables that need to be unique to instance of the program. Settings, Layout et

even more reason to use one db with an id for each user.

with your model what happens when you have a 100 users?

justindublin

amen wrote: »

even more reason to use one db with an id for each user.

with your model what happens when you have a 100 users?

I doubt I'll ever get up that high but I see your point, It would be best to future proof this now rather than later but like I said tough I'm a total noob at SQL.

Say for example I've two tables.

Settings Table - Which I need to be unique to each user.
Songlists Table - Which I need to be identical across all users.

How can users help me do that?
Baring in mind I cannot make any changes to the actual program.

All programs will go looking for the same table names at startup.

Giblet

A table can represent a UserId, SettingId, SettingValue structure, using a separate Settings table to hold all the available setting types?

But if you don't have much control over the source, might not be helpful.

The following basic table structures can hold all the setting information, user information, and the relationship between the two (Hence, relational database)

<Settings>
[SettingID],[SettingName],...,...

<User>
[UserID],[Username],...,...

<UserSettings>
[SettingID],[UserID],[SettingValue]