SQLTeam.com | Weblogs | Forums

One to One relationship with Id in both tables. Possible or should be avoided?

Hello,

Using MS SQL I created 3 tables: Reviews, Members and Workers.

Each Member or Worker has only 1 Review. And a Review can only be from 1 Member or 1 Worker.

So I created 1 to 1 relationship between Reviews and Members and Reviews and Workers.

CREATE TABLE Reviews (
  ReviewId INT NOT NULL IDENTITY PRIMARY KEY
);

CREATE TABLE Members (
  ReviewId INT NOT NULL,
    PRIMARY KEY (ReviewId),
    FOREIGN KEY (ReviewId) REFERENCES Reviews(ReviewId)
);

CREATE TABLE Workers (
  ReviewId INT NOT NULL,
    PRIMARY KEY (ReviewId),
    FOREIGN KEY (ReviewId) REFERENCES Reviews(ReviewId)
);

Question

I would like to get Members or Workers by each one Id and not by a ReviewId.

Can an Identity column be added to Workers and Members (WorkerId and MemberId)?

Shouldn't this column be a PK to be indexed and faster to get records?

Can WorkerId / MemberId be a PK when ReviewId is already a PK for the 1 to 1 relation?

Thank you