Best approach to handle multiple databases with referential integrity

Question

I am designing an online marketplace for a complex auction website.

Given the potential of growth and complexity of each database, I am separating the databases of members and products. The question is what to do with referential integrity between two separate databases? The relationships can be one-to-one or one-to-many. I have read quite a bit about using triggers, replicating tables as read-only from one database to another; obviously not to end up with orphan rows.

I know that there are similar questions on the site but none have a concrete answer.

Answer 1

Given the potential of growth and complexity of each database, I am separating the databases of members and products. The question is what to do with referential integrity between two separate databases?

This might be a defensible strategy, or it might be a disaster. It depends in part on your dbms. In most cases, you'd approach this kind of problem with multiple schemas, not multiple databases. (In this sense, a schema is what you get when you run a CREATE SCHEMA statement.) Foreign key references work fine in multiple schemas. Many dbms that support multiple SQL schemas don't support simple foreign key references to other database.

MySQL doesn't support CREATE SCHEMA. Instead, MySQL databases behave much like SQL schemas.

CREATE SCHEMA is a synonym for CREATE DATABASE.