I have a 2 databases, the "Database1" does have 10 tables from which we are handling the search by entering the respective keyword for the respective table. "Database2" doesn't have those 10 tables that are in "Database1", but now I want to do a search for the same 10 tables in "Database1" in "Database2". There are 2 options here
(i) Either we need to create those 10 tables again in "Database2"
(ii) In "Database2" we connect to "Database1" to access those 10 tables
Please let me know how I can go about it , I feel that the first option is better? if there is any alternative please let me know.
I'd say the correct answer depends on what you need. If the ten tables should always have the same contents in both databases, then linking them together somehow would probably be a good idea. If the ten tables should be independent in both databases, then you'll certainly need to create them in the second database too.
Also consider that linking two databases together might bring issues with distributed transactions (these usually only appear when something gets wrong), and if the first database is down, your second database might not be fully functional due to the link.