Is it acceptable to cross between databases?

Question

I'm not sure what this practice is actually called, so perhaps someone can edit the title to more accurately reflect my question.

Let's say we have a site that stores objects of different types. Each type of object has its own database (a database of books and assorted information with its tables, a database of CDs and information with its tables, and so on). However, all of the objects have keywords and the keywords should be uniform across all objects, regardless of type. A new database with a few tables is made to store keywords, however each object database is responsible for mapping the object ID to a keyword.

Is that a good practice?

Answer 1

Is there a reason to have separate databases for each type of object? You would be better off using multiple tables, and joining them. For example, you may have a table GENERIC_OBJECT which holds things that are common across all types, and then a table called BOOK_OBJECT where BOOK_OBJECT.ID = GENERIC_OBJECT.ID for a given book. Another table would be CD_OBJECT where CD_OBJECT.ID = GENERIC_OBJECT.ID for a given CD. Then things like keywords that are common across all objects would be stored in the GENERIC_OBJECT table, and things that are specific to the item would go in the item's corresponding table.

Answer 2

By separating them into different databases, you lose:

• the ability to do ACID transactions (assuming you aren't using a two-phase commit solution).
• the ability to have referential integrity.
• JOINs across tables.