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.

  • Review this answer. It covers the implementation details of modeling this kind of relationship. – Nick Chammas Jun 10 '12 at 18:26

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.

| improve this answer | |
  • 1
    Slightly OT: what did you use to make this ER diagram? – Daniel Serodio May 19 '15 at 17:57
  • @DanielSerodio - I used Visio with smart shapes I built myself based on the James Martin ERD notation. The shapes use a custom line texture to give them an informal appearance, which I find helpful in reminding people when a diagram is a "sketch" or draft design. – Joel Brown May 19 '15 at 23:02
  • @JoelBrown Would you be willing to share your stencils? These are really nice shapes – imoatama Oct 20 '16 at 1:42
  • 2
    @imoatama - It has been a while, but I finally got around to posting the stencil here: moosewarevisioerd.codeplex.com Note as in the description that the stencil's smart shapes were built for an older version of Visio and some of the behaviours of the relationship connector shapes can be a bit flakey. One day I'll get around to fixing this up. – Joel Brown Nov 20 '16 at 22:25

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.

| improve this answer | |
  • I would like OperatingSystem to be a separate entity as it is a specialization of Software. It may have attributes that no other Software will have (such as kernel type, RTOS-or-not flag, multiuser flag, etc.). – jl6 Jun 10 '12 at 17:26
  • 1
    @jl6 I still beg to differ here. Every piece of software (be it an OS or not) will have specific attributes. Those can be stored elsewhere. You're minimizing the scalability by keeping OS separate. – Thomas Stringer Jun 10 '12 at 17:32
  • If I understand correctly, you are recommending a Software entity, and SoftwareType entity. Are you saying Free, NonFree, and OperatingSystem are all just different instances of SoftwareType? I'm sure you're correct, but then where do you store the various attributes of the different types? – jl6 Jun 10 '12 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.