I'm building a database model that requires one table to have a relationship to only one of several other tables. For example, a Contract may be associated with a Person, a Business, or a Government, but only one of those.

Other than having a foreign key column for each of those and using a check constrain to ensure only one is populated, what is the best approach to this situation?

  • 2
    it should be good enough. But I'm thinking 'bout having one table stands for Person/Business/Goverment called Tender, it has Type which is Person or Bussiness or Goverment, and then Contract will have FK to Tender only. – Dat Nguyen Jun 24 '19 at 6:47
  • @datnguyen, I prefer the supertype variant which you describe as Tender. I think you should make it an answer – Lennart Jun 24 '19 at 8:54
  • How would that work if those entities are completely different and and not really suptypes of a class, other than this one relationship? – tim Jun 24 '19 at 18:07
  • @tim: In that case, you may have PersonExtraInfo, BusineesExtraInfo and GovermentExtraInfo tables to contains the diff info. Tender would have the common info of them, and I don't think there would be an entity that was not really suptypes of Tender. In case you'd like not to have 3 extra tables, like to denormalize it, you could put all info together in Tender. – Dat Nguyen Jun 25 '19 at 2:58
  • @tim You may find of help the design principles detailed in this Q & A and in this Q & A. – MDCCL Jun 27 '19 at 16:27

Your Answer

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.