CODEWITHSUNDEEP
sqlsql-servert-sql
Miguel Moura
Miguel Moura

Reputation: 39374

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

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?

Upvotes: 0

Views: 521

Answers (1)

rens
rens

Reputation: 43

Have a table ReviewOrigin (two rows Worker and Member). Review table can have ReviewID, ReviewOriginID and ReviewerID. ReviewerID is either MemberID or WorkerID.

Enforce a composite uniquess on ReviewOriginID and ReviwerID.

The IDs in all these tables can be IDENTITY. You need to index the columns based on your query needs. Start with PK of IDENTITY columns and created needed composite NCs on all IDs columns in Review table.

Upvotes: 1

Related Questions