2

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

Amarth Gûl
  • 121
  • 3

2 Answers2

0

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.

Damir Sudarevic
  • 1,242
  • 10
  • 12
0

It seems like you might be confusing object-oriented design with database design.

With database design, as with object oriented design, a good starting point is modelling things based on real-world concepts. Regarding the "OS" concept, you should ask yourself a couple of questions.

  1. Is "OS" a simple attribute comprising a single value, such as "Windows" or "Mac" or "Linux" etc? Or do you need to associate other information against each type of "OS"?

  2. Is "OS" something that will be a constrained to a fixed set of values values, or something that is extensible within the database?

Depending on the above two answers you will have an idea of how to proceed.

If you ONLY need to store the type of operating system, AND you do not need any extensibility, then you may only need a simple, non-relational property on your Machine table. eg. a simple code like MAC, WIN, LINUX etc.

If you need extensibility of the available operating systems, for example you don't want to just define Mac, Windows, or Linux, but specific versions or Distributions such as Mac Sierra, Windows 10 or Windows 11, Ubuntu 20, 22, or 24 -- the implication being you need to extend this table as new versions are released -- then you will want a separate table with the list of valid operating systems.

Such a table might have a "Code" and a "Name" attribute as a minimum. Your Machine table would then contain a FOREIGN KEY to the OS table to identify the OS. By employing a foreign key constraint you can then prevent illegal OS values being set in your Machine table.

So that brings us to the idea of what looks like the sub-types of Mac and Windows in your diagram. I am not familiar with that symbol in the diagram, so I don't know if you are trying to imply some kind of "is a" relationship, but to me it looks like you have tried to model an inheritance relationship, where OS is the base type of Mac and Windows.

It is unclear whether you are suggesting having separate Mac and Windows tables.

An immediate downside of having separate tables is that you are now structurally stuck with two explicit tables for EXACTLY two operating systems. If someone came along and needed a different operating system, you'd be toast.

So, unless you had a really good reason to do so (and by asking here, I will assume you don't), then you would stick to a single OS table.

The model therefore looks like (and this is what I would model):

[MACHINE] ---> [OS]

Where ---> represents the foreign key relationship between the two entities (the end opposite the arrowhead stores the foreign key code).

Now, if you need to store information ABOUT the operating systems beyond just a code/name, then you would store that information in the OS table. This would be information specific to the operating system, independent of the Machine using it. For example, a "Release Date" would be a property that would make sense to store on the OS entity.

The final aspect to this modelling is whether different operating systems are so "widely disparate" that you need to store different information for different classes of operating system and that disparity WARRANTS separate tables.

I would counsel you to heavily avoid that path until you are extremely sure of your needs. You are much better off, initially at least, having unused columns in a table [ie. not used by some classes of operating system] than trying to introduce different tables.

Kevmeister68
  • 49
  • 1