Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Unanswered: Inter Database Relations

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    4
    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

  4. #4
    Join Date
    Dec 2011
    Posts
    1

    It is possiable under MySQL

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •