Modeling an either/or relationship

Question

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?

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

Do you want to say that WINDOWS and MAC are considered as separate and independent entities which are stored in separate tables? — Akina, Sep 10 '20 at 04:41
@Akina I'm still learning DB so I'm not sure what that means. From what I know, I guess they are separated. Say a Dell Alienware installs a Win10 Home, Alienware would be an instance of the entity MACHINE, win10 home would be an instance of entity WINDOWS, and they have a relationship. But having that relationship also means this Dell machine cannot install Mac. — Amarth Gûl, Sep 10 '20 at 04:57
I see 2 entities - MACHINE and OS. So - 2 tables. First - OS table which is self-referenced and stores everything as aa tree (parent-child), i.e. Windows and Mac are root instances, Windows 10 is an instance which have parent = Windows, Windows 10 Home is an instance which have parent = Windows 10, and so on... Second - MACHINE table which references to an instance in OS table, for example John's workstation is an instance which references to an instance Windows 10 Home in OS table. — Akina, Sep 10 '20 at 05:06
I'm not sure you actually have subtypes or are missing an entity to enumerate which operating systems are offered by each manufacturer (and possible versions of each operating system)? — , Sep 10 '20 at 14:44
@bbaird I don't think manufacturer matters, think about choosing an OS to install on a computer, 2 OSs are available, but only 1 can be selected. Both OSs (WINDOWS and MAC in this case) are subclasses of the entity OS, and a relationship is needed to illustrate which OS is selected. — Amarth Gûl, Sep 10 '20 at 21:45
It may appear that way, but you're not going to have AppleOS on anything other than a Mac. But I think you've answered your own question - if only one OS can be selected, there's not a subtype, just two or more choices for OS. — , Sep 11 '20 at 13:10

score 0 · Answer 1 · answered Sep 28 '20 at 16:40

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.

Modeling an either/or relationship

1 Answers1