Data Model - Associate with either of 2 separate entities
Say we have a 'Property' entity. This 'Property' entity can be associated with either a 'Person' entity, or an 'Organization' entity (essentially, the owner of the property), but only one type. How would you implement that in the database? 'Person' and 'Organization' are separate entities with different attributes stored for each.
The foreign key in 'Property' would need to be either a Person id or an Organization id, but it can only have one or the other, so I don't necessarily want to have two separate fields for each. I would rather have a solution that is handled by database constraints, and not extra code written to handle it.
Any ideas? If it matters, I am using SQL Server 2008 R2, but you can be more abstract.
Re: Data Model - Associate with either of 2 separate entities
What about 1 table that hold all "Owners" weither a person or Orginization. That will FK to the Property table. There would also be 2 more child tables off this One is Person and the Other Orginization. These can be FKed to the Owners table also. There is a second field in the Owner table to say if this is a person or an Orginization record.
Re: Data Model - Associate with either of 2 separate entities
Sounds like something that can work. Thanks.