title: Relational data modeling for sub types
body: <p>I am learning the Relational Model and data modeling.<br>
  And I have some confusion in my mind regarding sub types.</p>
  
  <p>I know that data modeling is an iterative process and there are many different ways to model things.<br>
  But I don't know how to choose between different options.</p>
  
  <h1>Example</h1>
  
  <p>Suppose we want to model the <strong>particles</strong> (molecule, atom, proton, neutron, electron, ...).<br>
  Let's ignore Quark and other particles for simplicity.</p>
  
  <p>Since all particles of the same type behave the same, we are not going to model the individual particles.<br>
  Put it in another way, we are not going to store every Hydrogen atom.<br>
  Instead, we will store Hydrogen, Oxygen and other atom types.<br>
  What we are going to model is actually particle types and relationships between them.</p>
  
  <p>I am using the word "<strong>type</strong>" carelessly.<br>
  A Hydrogen atom is an instance. Hydrogen is a type. Hydrogen is also a type of Atom.<br>
  Yes, there is a hierarchy of types involved. And we are ignoring the lowest level (individual particles). </p>
  
  <h1>Approaches</h1>
  
  <p>I can think of several approaches to model them.</p>
  
  <h2>1. One table (relation, entity) for each type of things (particle types).</h2>
  
  <h3>1.1 The first approach that comes into my mind.</h3>
  
  <p><em>Proton</em> (Proton)<br>
  <em>Neutron</em> (Neutron)<br>
  <em>Electron</em> (Electron)  </p>
  
  <p><em>Atom</em> (Atom)<br>
  <em>Atom_Proton</em> (Atom, Proton, Quantity)<br>
  <em>Atom_Neutron</em> (Atom, Neutron, Quantity)<br>
  <em>Atom_Electron</em> (Atom, Electron, Quantity)</p>
  
  <p><em>Molecule</em> (Molecule)<br>
  <em>Molecule_Atom</em> (Molecule, Atom, Quantity)</p>
  
  <h3>1.2 Since there is only one kind of proton/neutron/electron, we can simplify it.</h3>
  
  <p><em>Atom</em> (Atom, ProtonQuantity, NeutronQuantity, ElectronQuantity)<br>
  <em>Molecule</em> (Molecule)<br>
  <em>Molecule_Atom</em> (Molecule, Atom, Quantity)</p>
  
  <p>In this simplified model, facts about <em>Proton</em> are lost.</p>
  
  <h2>2. All things in one table, with associative tables representing relationships between them.</h2>
  
  <h3>2.1 one associative table for each relationship</h3>
  
  <p><em>Particle</em> (Particle)</p>
  
  <p><em>Atom_Proton</em>(Particle, Particle, ProtonQuantity)<br>
  <em>Atom_Neutron</em>(Particle, Particle, NeutronQuantity)<br>
  <em>Atom_Electron</em>(Particle, Particle, ElectronQuantity)<br>
  <em>Molecule_Atom</em> (Particle, Particle, AtomQuantity)  </p>
  
  <h3>2.2 single associative table</h3>
  
  <p><em>Particle</em> (Particle)<br>
  <em>ParticleComposition</em> (Particle, Particle, Quantity)</p>
  
  <p>This simplification doesn't lose anything. I think it's better.<br>
  But if there're facts that are specific to <em>Atom_Proton</em>/<em>Atom_Neutron</em>/<em>Atom_Electron</em>, 2.1 may be better.</p>
  
  <h3>2.3 combine 2.1 and 2.2</h3>
  
  <p><em>Particle</em> (Particle)  </p>
  
  <p><em>Atom_Proton</em> (Particle, Particle, other attributes)<br>
  <em>Atom_Neutron</em> (Particle, Particle, other attributes)<br>
  <em>Atom_Electron</em> (Particle, Particle, other attributes)
  <em>Molecule_Atom</em> (Particle, Particle, other attributes)  </p>
  
  <p><em>ParticleComposition</em>(Particle, Particle, Quantity, other attributes)</p>
  
  <p>In this approach, common attributes about particle composition go in <em>ParticleComposition</em>,<br>
  while special attributes about particle composition go in special tables.</p>
  
  <h2>3. Use sub type tables.</h2>
  
  <h3>3.1 A table for base type <em>Particle</em>, and additional tables for sub types (<em>Atom</em>, <em>Molecule</em>, ...).</h3>
  
  <p><em>Particle</em> (Particle)</p>
  
  <p><em>Proton</em> (Particle, other attributes)<br>
  <em>Neutron</em> (Particle, other attributes)<br>
  <em>Electron</em> (Particle, other attributes)<br>
  <em>Atom</em> (Particle, other attributes)<br>
  <em>Molecule</em> (Particle, other attributes)</p>
  
  <p><em>Atom_Proton</em> (Particle, Particle, ProtonQuantity)<br>
  <em>Atom_Neutron</em> (Particle, Particle, NeutronQuantity)<br>
  <em>Atom_Electron</em> (Particle, Particle, ElectronQuantity)<br>
  <em>Molecule_Atom</em> (Particle, Particle, AtomQuantity)</p>
  
  <h3>3.2 We can also combine the <em>Atom_XXXQuantity</em> tables in Atom and remove <em>Pronton</em>/<em>Neutron</em>/<em>Electron</em>.</h3>
  
  <p><em>Particle</em> (Particle)</p>
  
  <p><em>Atom</em> (Particle, ProtonQuantity, NeutronQuantity, ElectronQuantity)<br>
  <em>Molecule</em> (Particle, other attributes)</p>
  
  <p><em>Molecule_Atom</em> (Particle, Particle, AtomQuantity)</p>
  
  <p>It's simpler, but information about <em>Proton</em>/<em>Neutron</em>/<em>Electron</em> is lost as in 1.2.</p>
  
  <h3>3.3 We can change the name of <em>Molecule_Atom</em> to make it more generic.</h3>
  
  <p><em>Particle</em> (Particle)</p>
  
  <p><em>Atom</em> (Particle, ProtonQuantity, NeutronQuantity, ElectronQuantity)<br>
  <em>Molecule</em> (Particle, other attributes)</p>
  
  <p><em>ParticleComposition</em> (Particle, Particle, Quantity)  </p>
  
  <p>This looks like 2.2, with additional tables for sub types (<em>Atom</em>, <em>Molecule</em>).<br>
  It seems 2.2 is a special case of 3.3.</p>
  
  <h3>3.4 We can combine all the above approaches and get a generic model.</h3>
  
  <p><em>Particle</em> (Particle)</p>
  
  <p><em>Proton</em> (Particle, other attributes)<br>
  <em>Neutron</em> (Particle, other attributes)<br>
  <em>Electron</em> (Particle, other attributes)<br>
  <em>Atom</em> (Particle, other attributes)<br>
  <em>Molecule</em> (Particle, other attributes)  </p>
  
  <p><em>ParticleComposition</em> (Particle, Particle, Quantity, other attributes)</p>
  
  <p><em>Atom_Proton</em> (Particle, Particle, other attributes)<br>
  <em>Atom_Neutron</em> (Particle, Particle, other attributes)<br>
  <em>Atom_Electron</em> (Particle, Particle, other attributes)<br>
  <em>Molecule_Atom</em> (Particle, Particle, other attributes)</p>
  
  <p>It seems that <em>Atom_Proton</em>, <em>Atom_Neutron</em>, <em>Atom_Electron</em> and <em>Molecule_Atom</em> can be thought of as <strong>sub types</strong> of <em>ParticleComposition</em>.</p>
  
  <p>This approach is the most complex one, it contains many tables but each table has its role.</p>
  
  <h1>Questions</h1>
  
  <ol>
  <li>Does any of the above designs break the rules of Relational Model?</li>
  <li>Which approach is the best? Does it depend on how we think about the data? Does it depend on the requirements?</li>
  <li><p>If it depends on the requirements, shall we choose the simplest design at first and then make it more generic to accommodate new requirements?<br>
  Although the resulting data models share a lot of similarities, the initial design may influence the <strong>naming</strong> of the tables/columns, and the <strong>domains</strong> of the keys are different.</p>
  
  <ul>
  <li>If we choose to use one table for each type of things, we could choose incompatible keys for <em>Atom</em> and <em>Molecule</em>, such as <em>atom weight</em> for <em>Atom</em> and <em>molecule name</em> for <em>Molecule</em>.</li>
  <li>If we choose to use the generic approach, we may choose a common key for all particles.<br>
  Changing Keys may have greater impact on the system, so it may not be easy to evolve from a simple design to a generic one.<br>
  What do you think?</li>
  </ul></li>
  </ol>
  
  <p><strong>PS:</strong> This may not be an appropriate example and the solutions may be problematic, and there may be more variations of the approaches, but you can get the point hopefully.<br>
  If you have better designs, please share with me.</p>
  
  <hr>
  
  <h1>Update 1</h1>
  
  <h2>What is the data to model?</h2>
  
  <p>Initially, I was trying to model the <strong>particles</strong> because</p>
  
  <ul>
  <li>I think there are sub-typing relationships between them, which is exactly what I am looking for.</li>
  <li>They are well-understood (?) by people.</li>
  <li>It is a good example of how people understand the world.</li>
  </ul>
  
  <p>Here is the picture in my mind.
  <img src="https://i.stack.imgur.com/5fYR5.png" alt="Particle Hierarchy"></p>
  
  <p>I didn't state this clearly because I was not very clear about what I was trying to model either.<br>
  Firstly I thought Atom is the parent of Proton/Neutron/Electron, and Molecule is the parent of Atom.<br>
  Then I realized that this is about composition, not about subtyping, and not about <strong>Type Hierarchy</strong>.  </p>
  
  <h2>Types</h2>
  
  <p>I have been thinking about types for a while, as well as grouping and classification.</p>
  
  <p>Here is a quote from "<em>SQL and Relational Theory</em>":</p>
  
  <blockquote>
    <p>So what is a type, exactly? In essence, it’s <em>a named, finite set of values</em> ─ all possible values of some specific kind: for example, all possible integers, or all possible character strings, or all possible supplier numbers, or all possible XML documents, or all possible relations with a certain heading (and so on).</p>
  </blockquote>
  
  <p>People coined the name "Integer" to represent the set of integer <em>values</em>.<br>
  Actually, people coined concepts and names to identify things, grouping things so that we can understand/model the world.  </p>
  
  <p>Proton is a set of real protons, Hydrogen is a set of hydrogen atoms, and so on.<br>
  In this sense, the real particles stay at the <strong>lowest level</strong> of the type hierarchy.<br>
  I was trying to model all the particles at first, but then I was stuck because</p>
  
  <ul>
  <li>I couldn't think of an appropriate key to identify each real particle;</li>
  <li>there are too many of them to store in a database.</li>
  </ul>
  
  <p>So I decided to ignore the real particles and model the types instead.</p>
  
  <p>When we say "a molecule is composed of atoms", it means "a real H2O molecule is composed of two real Hydrogen atoms and one Oxygen atom", it also means "any (type of) molecule is composed of (some types of) atoms".</p>
  
  <p><strong>Instead of stating every fact about the real particles, we can just state facts about the particle types.</strong><br>
  That is the benefit we get by grouping things and coined names (types).</p>
  
  <h2>Particle Type Hierarchy As Sets</h2>
  
  <p>The hierarchy can be translated into set definitions.  </p>
  
  <p><strong>Second level - types above the real particles:</strong></p>
  
  <pre><code>S_proton = { p | p satisfied the definition of a proton }  
  S_neutron = { n | n satisfied the definition of a neutron }  
  S_electron = { e | e satisfied the definition of an electron }  
  S_hydrogen = { h | h satisfied the definition of a hydrogen }  
  S_oxygen = { o | o satisfied the definition of an oxygen }  
  S_h2o = { w | w satisfied the definition of a h2o }  
  S_o2 = { o | o satisfied the definition of a o2 }
  </code></pre>
  
  <p><strong>Higher Levels</strong></p>
  
  <p>Using the terminology of Set Theory, type A is a subtype of B if A is a subset of B.</p>
  
  <p>I first thought we could define the Atom type as: </p>
  
  <pre><code>S_atom = S_hydrogen union S_oxygen union ...
  </code></pre>
  
  <p>However, the sets are relations and the elements are tuples, so the union doesn't work if tuples in the relations are incompatible.</p>
  
  <p>The approaches that use subtype tables solve the problem and models the subset relationship.</p>
  
  <p>But in the subtyping approach, Atom is still in the <strong>second level</strong>.</p>
  
  <p><img src="https://i.stack.imgur.com/oJSyR.png" alt="enter image description here"></p>
  
  <p>Higher level types are defined as sets of sets.  </p>
  
  <pre><code>S_atom = { S_hydrogen, S_oxygen, ... }
  S_molecule = { S_h2o, S_o2, ... }
  S_particle = { S_proton, S_neutron, S_electron, S_atom, S_molecule }
  </code></pre>
  
  <p>which means Particle is the type of Atom, and Atom is the type of Hydrogen.</p>
  
  <p>This way, the relationships between particles can be represented at a high level.</p>
  
  <h1>The new data model</h1>
  
  <h2>4. Treat types as a hierarchy of types</h2>
  
  <p><em>ParticleType</em> (ParticleType, Name)<br>
  <em>ParticleTypeHierarchy</em> (ParticleType, ParentType)<br>
  <em>ParticleComposition</em> (PartileType, SubParticleType, Quantity)</p>
  
  <p>Sample data:</p>
  
  <pre>
  ParticleType
  
  | ParticleType | Name     |
  |--------------+----------|
  | Particle     | Particle |
  | Proton       | Proton   |
  | Neutron      | Neutron  |
  | Electron     | Electron |
  | Atom         | Atom     |
  | Molecule     | Molecule |
  | H            | Hydrogen |
  | O            | Oxygen   |
  | H2O          | Water    |
  | O2           | Oxygen   |
  
  ParticleTypeHierarchy
  
  | ParticleType | ParentType |
  |--------------+------------|
  | Proton       | Particle   |
  | Neutron      | Particle   |
  | Electron     | Particle   |
  | Atom         | Particle   |
  | Molecule     | Particle   |
  | Hydrogen     | Atom       |
  | Oxygen       | Atom       |
  | H2O          | Molecule   |
  | O2           | Molecule   |
  
  ParticleComposition
  
  | PartileType | SubParticleType | Quantity |
  |-------------+-----------------+----------|
  | H           | Proton          |        1 |
  | H           | Electron        |        1 |
  | He          | Proton          |        2 |
  | He          | Neutron         |        2 |
  | He          | Electron        |        2 |
  | H2O         | H               |        2 |
  | H2O         | H               |        2 |
  | H2O         | O               |        1 |
  | CO2         | C               |        1 |
  | CO2         | O               |        2 |
  
  </pre>
  
  <p>For comparison, this is the sample data for a subtype table approach.</p>
  
  <pre>
  
  Particle
  
  | ParticleId | ParticleName   |
  |------------+----------------|
  | H          | Hydrogen       |
  | He         | Helium         |
  | Li         | Lithium        |
  | Be         | Beryllium      |
  | H2O        | Water          |
  | O2         | Oxygen         |
  | CO2        | Carbon Dioxide |
  
  Molecule
  
  | MoleculeId | some_attribute |
  |------------+----------------|
  | H2O        | ...            |
  | O2         | ...            |
  | CO2        | ...            |
  
  Atom
  
  | AtomId | ProtonQuantity | NeutronQuantity | ElectronQuantity |
  |--------+----------------+-----------------+------------------|
  | H      |              1 |               0 |                1 |
  | He     |              2 |               2 |                2 |
  | Li     |              3 |               4 |                3 |
  | Be     |              4 |               5 |                4 |
  
  ParticleComposition
  
  | ParticleId | ComponentId | Quantity |
  |------------+-------------+----------|
  | H2O        | H           |        2 |
  | H2O        | O           |        1 |
  | CO2        | C           |        1 |
  | CO2        | O           |        2 |
  | O2         | O           |        2 |
  
  </pre>
  
  <h3>sub-atom</h3>
  
  <p>These particle types are defined by people and people keep defining new concepts to model new aspects of the reality.<br>
  We can define "sub-atom" and the hierarchy will look like:</p>
  
  <p><img src="https://i.stack.imgur.com/Ubpzv.png" alt="Particle Type Hierarchy with Sub-Atoms"></p>
  
  <p>Approach 4 can accommodate this type hierarchy change more easily.  </p>
  
  <hr>
  
  <h1>Update 2</h1>
  
  <h2>The facts to record</h2>
  
  <ol>
  <li>There are different types of particles in the world: protons, neutrons, electrons, atoms, molecules.</li>
  <li>Atoms are composed of protons, neutrons, and electrons.</li>
  <li>Molecules are composed of atoms.</li>
  <li>There are many different types of atoms: Hydrogen, Oxygen, ....</li>
  <li>There are many different types of molecules: H2O, O2, ....</li>
  <li>A Hydrogen atom is composed of one proton and one electron; ...</li>
  <li>A H2O molecule is composed of two Hydrogen atoms and one Oxygen atom; ...</li>
  <li>Different types of particles may have special properties, e.g. An atom has atom weight, etc.</li>
  <li>...</li>
  </ol>