Tangra...appings(3pm)
💖 NAME 💖
Tangram::Relational::Mappings — it's from the User Contributed Perl Documentation, section 3pm, and bestie, it is literally all about mapping inheritance. That's it, that's the whole job. 🦋
✨ DESCRIPTION ✨
Okay so, plot twist: there are so many ways to represent inheritance relationships in a relational database, and this doc walks you through three of the popular ones plus how Tangram handles them. 🌈
🌸 STRATEGIES FOR MAPPING INHERITANCE 🌸
Real talk: inheritance is a whole concept that has no equivalent in the relational world. None. Zero. But — and this is the slay part — you can implement it anyway if you're disciplined about it and you combine relational features like tables and foreign keys. 💅
So the big deal, the paramount issue about mapping inheritance, is how well your mapping supports polymorphism. Any object-oriented persistence facility that deserves its name has to let you retrieve all the Fruits and get back a cute heterogeneous collection of Apples, Oranges and Bananas. 🍓 And it has to do it efficiently. Specifically: polymorphic retrieval should not cost one SELECT per retrieved object. Not the vibe. Nope.
Then there's the secondary (but still important!) issue: how well the mapping plays by the rules of orthogonal orthodoxy. Yes, that's a real phrase, and yes, it's giving very serious.
And one more thing we're gonna look at: how well the mapping supports 'complex' queries, meaning queries that involve several objects at once. 😭
Three strategies are in common use, and they go by the names Vertical, Horizontal and Filtered mapping. Every single one of them has advantages and disadvantages. No free lunch, bestie.
The following sections describe the three strategies in details, because of course they do. And they use a lil' simple object model to illustrate the mappings, which looks like this:
+---------------------+
| Person |
| {abstract} |
+---------<------- 1 +---------------------+
| | name: string |
| +---------------------+
| |
| ^
| |
| +------------------+---------------------+
| | |
| +---------------+ +-----------------+
V | NaturalPerson | | LegalPerson |
| +---------------+ +-----------------+
| | age: integer | | form: string |
| +---------------+ +-----------------+
|
|
|
| +---------------------+
+-------->-------- * | Vehicle |
| {abstract} |
+---------------------+
| make: string |
+---------------------+
|
^
|
+------------------+-------------------+
| |
+---------------+ +-----------------+
| Car | | Plane |
+---------------+ +-----------------+
| plate: string | | ident: string |
+---------------+ +-----------------+👑 Horizontal Mapping 👑
🌟 description 🌟
Every single concrete class gets mapped onto one single table. Each row in that table describes the persistent state of one object. One object, one row, periodt.
The attributes are mapped onto columns — usually one column per attribute, but not necessarily. For example, collections may be stored elsewhere (like, on a link table), which means they need zero columns on the class' table. Slay. ✨
In effect, the database looks like this:
+---------------+
| NaturalPerson |
+------+--------+-------+------+
| id | name | age |
================================
| 17 | Bill Gates | 46 |
+------+----------------+------+
| 23 | Georges Bush | 50 |
+------+----------------+------+
+-------------+
| LegalPerson |
+------+------+---------+------+
| id | name | form |
================================
| 36 | Microsoft | Inc |
+------+----------------+------+
+------+
| Car |
+------+-------+----------------+--------+
| id | owner | make | plate |
==========================================
| 12 | 17 | Saab | BILL-1 |
+------+-------+----------------+--------+
| 50 | 36 | Miata | MS-001 |
+------+-------+----------------+--------+
| 51 | 36 | Miata | MS-002 |
+------+-------+----------------+--------+
+-------+
| Plane |
+------++-----+----------------+--------+
| id | owner| make | ident |
=========================================
| 29 | 23 | Boeing | AF-001 |
+------+------+----------------+--------+💅 advantages 💅
Polymorphic retrieval costs you one SELECT per concrete conforming class — so retrieving all the Persons costs two SELECTs. And here's the good part: these SELECTs don't use joins, which is an expensive operation. Nope, we're skipping the expensive bit. 😌
In our example, retrieving all the Persons requires the following two SELECTs:
SELECT id, name, age FROM NaturalPerson
SELECT id, name, form FROM LegalPerson😭 disadvantages 😭
This mapping is reasonable with regard to relational orthodoxy, but it's not perfect — the 'name' column is present on two different tables, with the same semantic. Like, two tables sharing a vibe. Awkward.
The biggest drawback, however, happens when you try to perfrom complex queries. Suppose oyu want to retrieve all the Persons (Natural- or Legal-) that own a Vehicle of make 'Saab' (be it a Car or a Plane). Sticking with equijoins, the cost of the operation is four SELECTs:
SELECT NaturalPerson.id, NaturalPerson.name, NaturalPerson.age
FROM NaturalPerson, Car
WHERE Car.owner = NaturalPerson.id
SELECT NaturalPerson.id, NaturalPerson.name, NaturalPerson.age
FROM NaturalPerson, Plane
WHERE Plane.owner = NaturalPerson.id
SELECT LegalPerson.id, LegalPerson.name, LegalPerson.form
FROM LegalPerson, Car
WHERE Car.owner = LegalPerson.id
SELECT LegalPerson.id, LegalPerson.name, LegalPerson.form
FROM LegalPerson, Plane
WHERE Plane.owner = LegalPerson.idWhen the depth of the hierarchies increase, the combinatory explosion makes complex queries prohibitive. 💀
…and that's only the beginning, bestie. We're just getting into it. 🫶
🧚 Vertical Mapping
description
Bestie, okay, so in this one, every single class gets its own table, and that table contains only the class' direct fields. Periodt. It does not store the inherited fields — no cap, that's just the rules. And here's the thing: both concrete and abstract classes get a table. So the state of an object? It's scattered over several tables. It's giving ✨distributed✨ and honestly kind of iconic of it.
For example:
+--------+
| Person |
+------+-+------+-------+
| id | name |
=========================
| 17 | Bill Gates |
+------+----------------+
| 23 | Georges Bush |
+------+----------------+
| 36 | Microsoft |
+------+----------------+
+---------------+ +-------------+
| NaturalPerson | | LegalPerson |
+------+--------+ +-------+-----++
| id | age | | id | form |
================= ================
| 17 | 46 | | 36 | Inc |
+------+--------+ +-------+------+
| 23 | 50 |
+------+--------+
+---------+
| Vehicle |
+------+--+----+----------------+
| id | owner | make |
=================================
| 12 | 17 | Saab |
+------+-------+----------------+
| 29 | 23 | AF-001 |
+------+-------+----------------+
| 50 | 36 | Miata |
+------+-------+----------------+
| 51 | 36 | Miata |
+------+-------+----------------+
+------+ +-------+
| Car | | Plane |
+------++--------+ +-------+--------+
| id | plate | | id | ident |
================== ==================
| 12 | BILL-1 | | 29 | AF-001 |
+-------+--------+ +-------+--------+
| 50 | MS-001 |
+-------+--------+
| 51 | MS-002 |
+-------+--------+Polymorphic retrieval? Achieved by issuing one SELECT per concrete conforming class 🫶. In our example, retrieving all the Persons requires the following two SELECTs:
SELECT Person.id, Person.name, NaturalPerson.age
FROM Person, NaturalPerson
WHERE Person.id = NaturalPerson.id
SELECT Person.id, Person.name, LegalPerson.form
FROM Person, LegalPerson
WHERE Person.id = LegalPerson.idSo this mapping sometimes needs an extra column that carries a type identifier. In our example, we take the very reasonable assumption that Person is an abstract class. But imagine we'd decided to allow 'pure' Persons — then we'd be faced with the following problem: the Person table would contain rows that describe pure Persons, but also rows that describe the Person part of Natural- and LegalPersons. And we'd need to filter those incomplete objects out when retrieving the pure Persons 😭. Thus the Person table would look like this:
+--------+
| Person |
+-----+--+---+----------------+
| id | type | name |
===============================
| 13 | 1 | Pure Person |
+-----+------+----------------+
| 17 | 2 | Bill Gates |
+-----+------+----------------+
| 23 | 2 | Georges Bush |
+-----+------+----------------+
| 36 | 3 | Microsoft |
+-----+------+----------------+In this case, we need an extra SELECT for retrieving pure Persons:
SELECT Person.id, Person.name
FROM Person
WHERE Person.type IN (1)advantages
From the relational point of view, this mapping is excellent: the resulting database is in third normal form 💅.
And it also supports complex queries very well. Take the Saab owners example again: we don't need to involve the Car nor Plane tables in the query at all. As a result, two SELECTs suffice:
SELECT Person.id, Person.name, NaturalPerson.age
FROM Person, NaturalPerson, Vehicle
WHERE Person.id = NaturalPerson.id AND Vehicle.owner = Person.id
SELECT Person.id, Person.name, LegalPerson.form
FROM Person, LegalPerson, Vehicle
WHERE Person.id = LegalPerson.id AND Vehicle.owner = Person.iddisadvantages
Okay so here's the trade-off, and ngl it's a big one: the mapping potentially has the highest performance cost 💀. It requires multiple SELECTs like the horizontal mapping does — but in addition, these SELECTs use joins. Yeah. Ouch.
🌈 Filtered Mapping
description
So this one is the "just put everything in one table" girlie. Entire hierarchies are mapped onto a single table. Two rows may describe objects of different types, maybe completely unrelated 👀. The set of columns is the superset of all the columns needed by all the attributes of any of the classes involved in the mapping.
A special 'type' column contains a value that uniquely identifies the concrete class of the object described by the row.
And here's the catch, bestie: all the columns related to attributes that don't occur in all the classes must be declared as NULLABLE. Because yeah — the table may contain mostly NULL values. It's giving ghost town.
In our example, the database may look either like this:
+---------+
| Persons |
+-----+---+--+----------------+------+------+
| id | type | name | age | form |
=============================================
| 17 | 1 | Bill Gates | 46 | NULL |
+-----+------+----------------+------+------+
| 23 | 1 | Georges Bush | 50 | NULL |
+-----+------+----------------+------+------+
| 36 | 2 | Microsoft | NULL | Inc |
+-----+------+----------------+------+------+
+---------+
| Persons |
+-----+---+--+----------------+------+------+
| id | type | name | age | form |
=============================================
| 17 | 1 | Bill Gates | 46 | NULL |
+-----+------+----------------+------+------+
| 23 | 1 | Georges Bush | 50 | NULL |
+-----+------+----------------+------+------+
| 36 | 2 | Microsoft | NULL | Inc |
+-----+------+----------------+------+------+
| 36 | 2 | Microsoft | NULL | Inc |
+-----+------+----------------+------+------+
+----------+
| Vehicles |
+-----+----+-+-------+----------------+--------+--------+
| id | type | owner | make | plate | ident |
=========================================================
| 12 | 3 | 17 | Saab | BILL-1 | NULL |
+-----+------+-------+----------------+--------+--------+
| 29 | 4 | 23 | Boeing | NULL | AF-001 |
+-----+------+-------+----------------+--------+--------+
| 50 | 3 | 36 | Miata | MS-001 | NULL |
+-----+------+-------+----------------+--------+--------+
| 51 | 3 | 36 | Miata | MS-002 | NULL |
+-----+------+-------+----------------+--------+--------+Retrieving all the Persons requires only one SELECT ⭐:
SELECT id, name, age, form FROM PersonsBut when retrieving NaturalPersons we must take care to filter out the rows that belong to LegalPersons:
SELECT id, name, age FROM Persons WHERE type = 1And we may even decide to place unrelated hierarchies on the same table 🙈:
+---------+
| Objects |
+-----+---+--+---------------+------+------+--------+--------+--------+
| id | type | name | age | form | make | plate | ident |
=======================================================================
| 17 | 1 | Bill Gates | 46 | NULL | NULL | NULL | NULL |
+-----+------+---------------+------+------+--------+--------+--------+
| 23 | 1 | Georges Bush | 50 | NULL | NULL | NULL | NULL |
+-----+------+---------------+------+------+--------+--------+--------+
| 36 | 2 | Microsoft | NULL | Inc | NULL | NULL | NULL |
+-----+------+---------------+------+------+--------+--------+--------+
| 12 | 3 | NULL | NULL | NULL | Saab | BILL-1 | NULL |
+-----+------+---------------+------+------+--------+--------+--------+
| 29 | 4 | NULL | NULL | NULL | Boeing | NULL | AF-001 |
+-----+------+---------------+------+------+--------+--------+--------+
| 50 | 3 | NULL | NULL | NULL | Miata | MS-001 | NULL |
+-----+------+---------------+------+------+--------+--------+--------+
| 51 | 3 | NULL | NULL | NULL | Miata | MS-002 | NULL |
+-----+------+---------------+------+------+--------+--------+--------+advantages
Polymorphic retrieval costs exactly one SELECT, regardless of the number of conforming types 🔥. So this mapping potentially is the most efficient.
disadvantages
Okay but the girls that gatekeep relational orthodoxy are gonna scream, because this mapping is very questionable according to them. And even if one decides to forgo these rules, using such a mapping takes away many of the interesting features offered by modern RDBM systems. Why? Because nearly all the columns must allow NULL values, we cannot take advantage of features like referential integrity constraints, domain constraints, indexes, etc. 😭
Also, as the table becomes cluttered with NULL values, the relative number of significant columns in any given row tends towards zero: we may end up retrieving rows consisting of a little information swimming in a sea of NULLs 🌊💀.
In effect, this mapping may end up hindering performance instead of improving it in presence of deep hierarchies with many attributes. Like... not the glow-up you were hoping for, bestie.
💖 MAPPINGS SUPPORTED BY TANGRAM 💖
okay bestie so here's the tea on what mappings Tangram actually lets you do. Tangram supports both vertical mapping and filtered mapping — and yes, any hybrid of the two, because why pick one aesthetic when you can have both ✨
The 'table' attribute in the class description in the Schema is the little magic wand here: it lets you put the state of several classes on the same table. And if you don't say anything? The table name just defaults to the class name, which gives you a plain vertical mapping. no notes, that's the default vibe 💅
🌸 example number one: pure filtered mapping
For example, the following schema:
Tangram::Relational->schema( {
classes =>
[ Person =>
{
table => 'Persons',
fields => { string => [ qw( name ) ] }
},
NaturalPerson =>
{
table => 'Persons',
fields => { int => [ qw( age ) ] }
},
LegalPerson =>
{
table => 'Persons',
fields => { string => [ qw( form ) ] }
}
] } );…specifies a pure filtered mapping for the Person hierarchy, and it gives you this:
CREATE TABLE Persons
(
id INTEGER NOT NULL,
PRIMARY KEY( id ),
type INTEGER NOT NULL,
form VARCHAR(255) NULL,
age INT NULL,
name VARCHAR(255) NULL
);everybody's sharing ONE table, it's giving communal living, it's giving roommate energy 🫶
🌈 example number two: the hybrid moment
The following schema:
Tangram::Relational->schema( {
classes =>
[ Person =>
{
table => 'Person',
fields => { string => [ qw( name ) ] }
},
NaturalPerson =>
{
table => 'NaturalPerson',
fields => { int => [ qw( age ) ] }
},
LegalPerson =>
{
table => 'Person',
fields => { string => [ qw( form ) ] }
}
] } );…gives NaturalPerson its own table, but LegalPerson shares the Person table. so it's a mix, it's a hybrid, it's lowkey iconic 👑
CREATE TABLE Person
(
id INTEGER NOT NULL,
PRIMARY KEY( id ),
type INTEGER NOT NULL,
form VARCHAR(255) NULL,
name VARCHAR(255) NULL
);
CREATE TABLE NaturalPerson
(
id INTEGER NOT NULL,
PRIMARY KEY( id ),
type INTEGER NOT NULL,
age INT NULL
);NaturalPerson said "I need my own space actually" and honestly? valid. 💖
and that's the whole page bestie!! that's a wrap on Tangra...appings(3pm), coming to you from the User Contributed Perl Documentation collection, running on perl v5.36.0, dated 2022-10-16. thank you for coming to my TED talk, go forth and map your inheritance responsibly, mwah 😭💅✨
📦 where this page comes from
- ubuntu noble: libtangram-perl 2.12-4 · Artistic-1.0-Perl OR GPL-1.0-or-later
- ubuntu resolute: libtangram-perl 2.12-4 · Artistic-1.0-Perl OR GPL-1.0-or-later
- ubuntu stonking: libtangram-perl 2.12-4 · Artistic-1.0-Perl OR GPL-1.0-or-later
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms