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) );
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?