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.

Improve this question
1
  • Review this answer. It covers the implementation details of modeling this kind of relationship.
    – Nick Chammas
    Commented Jun 10, 2012 at 18:26

2 Answers 2

Reset to default
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.

Improve this answer
4
  • 1
    Slightly OT: what did you use to make this ER diagram?
    – Daniel Serodio
    Commented May 19, 2015 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
    Commented May 19, 2015 at 23:02
  • @JoelBrown Would you be willing to share your stencils? These are really nice shapes
    – imoatama
    Commented Oct 20, 2016 at 1:42
  • 3
    @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
    Commented Nov 20, 2016 at 22:25
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.

Improve this answer
3
  • 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
    Commented Jun 10, 2012 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
    Commented Jun 10, 2012 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
    Commented Jun 10, 2012 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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