Inter Database Relations

davexpt · #1 (**permalink**) 07-31-09, 06:56

Hi there,

I have a problem, well, I'm developing a web application, in fact a group of web applications and I was thinking about the database structure of those applications,
I was puzzled with a thing that never occurred to me, is it possible to create inter
database relations, like individual databases for each application and a database to store common data to all? and if it is, do you think it is a good practice to do so? and how can that be achieved? well I can enforce logical relations through the various applications without using foreign key constraints but that could lead to orphan data problems.

Sure I could create a database common to all applications and work with that, but, I was just thinking in a scenario that never occurred to me, and couldn't find information about that anywhere.

r937 · #2 (**permalink**) 07-31-09, 07:08

Quote:

Originally Posted by davexpt

is it possible to create inter database relations, like individual databases for each application and a database to store common data to all?

of course it's possible, but i don't like the idea

it shouldn't take too long to test it, to see whether you can actually declare a foreign key across databases (my guess is no, but i could be wrong)

anyhow, why did you want to do this?

perhaps you might like to do a search for "premature optimization" and see what other people have said about this...

davexpt · #3 (**permalink**) 07-31-09, 07:19

Just exploring possibilities

well I thought so, it would be hell to maintain such a structure,
but it was a question that poped out of my mind and I I'm the kind of person who cannot rest with doubt ahah, but you're right, it would probably be a waste of time without significant benefits

anyway thanks for the answer

adam.g.pullen · #4 (**permalink**) 12-22-11, 21:44

I know that this thread is most likely dead but i was also searching for something similar to davexpt

Multiple applications sharing common data and wanting to maintain referential integrity.

So i did a test in MySQL:

Code:

drop database db2;
drop database db1;
create database db1;
create database db2;

use db1;
create table test (id int primary key);
use db2;
create table test(id int key, fkey int);
alter table test add foreign key(fkey) references db1.test(id);

insert into db1.test(id) values (1);
insert into db2.test(id,fkey) values(1,1);
insert into db2.test(id,fkey) values(2,2);

Fails, as it should, on the last command

Internet Services

Web Development

Digital Marketing

Consumer Tech