I have a backend with a microservice architecture. Each microservice has its own database. There is a relational database (like postgres) for users with a users table. There is a second same database for orders with a orders table. How to refer in the orders table to creator_id - the ID of the user from the other database who created the order. It is very important that the data is not duplicated!
P.S. And does it make sense to create a separate database for 1-2 tables? Maybe we should combine everything into one database? Then the tables should be put in one schema or in two different ones?
I was thinking about doing a shared database. But this, as far as I know, contradicts the principle of microservices. If it is wrong, please correct me. I don't have a very big project, but I'd like to try some good architectural practices.
If you are using Postgree, you can create a different schema for each database and make the relationship between them using foreign key. Example:
CREATE TABLE database2.schema2.table2 ( id serial PRIMARY KEY, table1_fk_id integer REFERENCES database1.schema1.table1(id)