One table used in two databases

Question

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:

1. Writing trigger on update/delete/insert one table and update another same time.
2. Creating view on product table and used in second db (I don't know how)
3. replication (I don't know how)

Can you guide me on the best way?

Answer 1

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:

CREATE SYNONYM dbo.CommonTableName FOR db1.dbo.CommonTableName

Answer 2

Create a synonym in the second database that points to the table in the first database. This way you can use the table from both databases for any operation (Insert, update, delete, select etc) seamlessly.

    CREATE DATABASE FirstDB ;
    CREATE DATABASE SecondDB ;
    GO
    USE FirstDB;
    GO
    CREATE TABLE Products(id INT IDENTITY PRIMARY KEY);
    GO
    USE SecondDB;

    -- Create a synonym to be able to reach the product table from the second database
    CREATE SYNONYM dbo.Products FOR FirstDB.dbo.Products;

    -- Add some values to the common table, available from both databases
    INSERT Products DEFAULT VALUES;
    INSERT Products DEFAULT VALUES;
    INSERT Products DEFAULT VALUES;

    -- any statement is available for both databases
    SELECT * FROM FirstDb.dbo.Products;
    SELECT * FROM SecondDB.dbo.Products;