I've three fundamental kinds of organizations: People, Companies, and Assets. Each Resource could be possessed by only one individual or Business. Each Individual and Business can own from to a lot of Assets. What will be the best practice for storing this kind of conditional relationship in Microsoft SQL Server?
My primary plan's to possess two nullable foreign secrets within the Assets table, one for individuals and something for Companies. One of these simple values is going to be null, as the other points towards the owner. The issue I see with this particular setup is it requires application logic to be able to be construed and enforced. Is really the perfect solution or exist other available choices?
You do not need application logic to enforce this. The simplest strategy is having a check constraint:
(PeopleID is null and BusinessID is not null) or (PeopleID is not null and BusinessID is null)