0

I have a scenario where records in the dependant table(say History) are created by two entities, one User and other Application. For which my purposed design is

User
-----------------
UserId (PK) BIGINT NOT NULL
Name NVARCHAR(200) NOT NULL
...

Application
-----------------
AppId (PK) BIGINT NOT NULL
Name NVARCHAR(200) NOT NULL
...

History
-----------------
HistoryId (PK) BIGINT NOT NULL
RelatedTableId BIGINT NOT NULL
RelatedTableType VARCHAR(20) NOT NULL
...

With the above design, I can not have FKs (declarative RI) which is one concern and not sure what is the limitation in the future. What would be the best design for this scenario? Looking for a better alternative before its too late.

The closes StackOverflow Q/A is this(dbfiddle) but I still want to know for this particular case. I am also hearing from other sources that we can use the trigger to enforce RI together with some codetype and codevalue design which I do not understand.

I am using MSSQL 2016 or above.

New contributor
Premchandra Singh is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • have you considered using of two separate history tables which could be combined into view with union all? – Nikita Jun 29 at 9:41
  • What is the expected behaviour in HISTORY when a USER is deleted? – Lennart Jun 29 at 11:48
  • Hi Nikita I didn't consider that way. I looking for a good design. @Lennart Ideally that should not be able to perform. But in my current design, it will be. I hear some source we can enforce RI(Referential Integrity) with the trigger but not sure and don't understand how effective it is. Still trying to gether more info. I appreciate your replies. Thanks – Premchandra Singh 2 days ago

Your Answer

Premchandra Singh is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.