I have a common product table in db1
and db2
. I want to make it one table because it has one inventory (stock of products). What is the best solution to share product table among two different database? I have few thoughts:
- Writing trigger on update/delete/insert one table and update another same time.
- Creating view on product table and used in second db (I don't know how)
- replication (I don't know how)
Can you guide me on the best way?
Best Answer
I would just make a synonym in db2 (assuming the table exists in db1). This will let you point directly to the source table without obfuscating behind a view, and will prevent you from needing to maintain a view if the schema of the table changes: