Modeling an either/or relationship

database-designrelations

I've seen this post (How should I model an “either/or” relationship?) but it's not exactly what I'm looking for. both answers are suggesting creating a subtype instead of a relationship.

Say I have an entity MACHINE, and I want to creat a relationship to connect it with another entity OS, call it "installs" or whatsoever. And this OS has 2 subtypes: WINDOWS and MAC (Linux and Unix also work but just for demo purpose they are not included). Not considering virtual machine or double OS, I can only choose one of these 2 subtypes of OS, how should I model this in the entity-relationship model?

demo img

Should I

  • Create 1 relationship between MACHINE and OS. Or

  • Create 2 relationship between MACHINE and WINDOWS, MACHINE and MAC. Or

  • Create 1 ternary relationship between MACHINE, WINDOWS and MAC.

And should I add additional attributes to the entities or the relationship?

Thanks:D

Best Answer

The most powerful design tool is still natural language, predicates, and constraints. So, when in doubt use a plain text editor. Simply focus on logic, as opposed to jargon (this-that relationship ..). The following is one possible version of this story:

-- Machine type TYP exists.
--
mtyp {TYP}
  PK {TYP}


-- data sample
  (TYP)
-------------------
  ('dell alienware')
, ('macbook pro')
, ('thinkpad')
, ('hp spectre')

-- Operating system OS exists.
--
osys {OS}
  PK {OS}


-- data sample
  (OS)
-------------
  ('windows')
, ('mac os')
, ('linux')
, ('unix')

-- Operating system OS is available for machine type TYP.
--
mtyp_osys {TYP, OS}
       PK {TYP, OS}

FK1 {TYP} REFERENCES mtyp {TYP}
FK2 {OS}  REFERENCES osys {OS}


-- data sample
  (TYP, OS)
-------------------------------
  ('dell alienware', 'windows')
, ('macbook pro'   , 'mac os')
, ('thinkpad'      , 'linux')
, ('thinkpad'      , 'windows')
, ('hp spectre'    , 'windows')

-- Machine number MCH# is of machine type TYP.
--
machine {MCH#, TYP}
     PK {MCH#}
     SK {MCH#, TYP}

FK1 {TYP} REFERENCES mtyp {TYP}


-- data sample
  (MCH#, TYP)
-------------------------------
  (1, 'dell alienware')
, (2, 'dell alienware')
, (3, 'dell alienware')
, (4, 'macbook pro')
, (5, 'thinkpad')
, (6, 'thinkpad')
, (7, 'hp spectre')

-- Operating system OS is installed
-- on machine number MCH#, machine type TYP.
--
install {MCH#, TYP, OS}
     PK {MCH#, TYP}

FK1 {MCH#, TYP} REFERENCES machine   {MCH#, TYP}
FK2 {TYP, OS}   REFERENCES mtyp_osys {TYP, OS}


-- data sample
  (MCH#, TYP)
-------------------------------
  (1, 'dell alienware', 'windows')
, (2, 'dell alienware', 'windows')
, (3, 'dell alienware', 'windows')
, (4, 'macbook pro'   , 'mac os')
, (5, 'thinkpad'      , 'windows')
, (6, 'thinkpad'      , 'linux')

-- at this point nothing is yet installed on machine # 7

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Using suffix # to save on screen space.
OK for SQL Server and Oracle, for others use _NO.
For example, rename MCH# to MCH_NO.

Here is another example of design using natural language, predicates, and constraints to keep a DB in high NF.

Related Question