How should I model an "either/or" relationship?

Say I have an entity named Software and two sub-types FreeSoftware and NonFreeSoftware. The NonFreeSoftware entity has attributes such as purchase date, vendor, etc.. The FreeSoftware entity has attributes such as license, source code url, etc.

So if I want to model another entity, OperatingSystem, how should I do it? There is an "is a" relationship to Software but an "either/or" relationship to FreeSoftware and NonFreeSoftware.

I think I'm missing something in the way I'm analyzing this hierarchy.

The way to manage this is that your sub-types have to be determined by the super-type (i.e. the PK of the sub-type is also a FK from the sub-type to the super-type.)

The challenge is understanding whether something is truly mutually exclusive or not. The attributes of sub-types should apply only to those sub-types, but it may well be that some sub-types are mutually exclusive and some aren't.

If you have some mutually exclusive sub-types, then you can use a partitioning attribute on the super-type to indicate which of the (two or more) mutually exclusive sub-types apply. This partitioning attribute can be used in with constraints or triggers to enforce the mutual exclusivity.

If you have sub-types that are not mutually exclusive, then they can exist without using any partitioning attribute.

Consider this data model:

You have three super-types, but the FREE_SOFTWARE and NON-FREE_SOFTWARE types are mutually exclusive, based on the SOFTWARE.free_not_free flag partitioning attribute. Any given piece of software is also potentially an OPERATING_SYSTEM, regardless of whether or not it is free.

StackExchange Database Administrators Q#19034, answer score: 9