15

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.

MDCCL
  • 8,530
  • 3
  • 32
  • 63
jl6
  • 815
  • 7
  • 15

2 Answers2

9

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:

ERD

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.

Joel Brown
  • 12,604
  • 2
  • 32
  • 46
1

Why would OperatingSystem be a completely new entity? It should fall under the Software one, as that is what it is. And an OS (if closed-source) would have a purchase date, vendor, etc. And open-source OS would have a license, source code URL, etc.

I would recommend a relationship to a SoftwareType or something along those lines. That's when you could/should specify whether the Software is an OS, or application, or whatever other types of software you're supporting.

Thomas Stringer
  • 42,434
  • 9
  • 120
  • 155