Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Review this answer. It covers the implementation details of modeling this kind of relationship. – Nick Chammas Jun 10 '12 at 18:26
up vote 3 down vote accepted

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.

share|improve this answer
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

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.

share|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
@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 posting your answer, you agree to the privacy policy and terms of service.

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