Representing a Discriminated Union in SQL

Let’s say you’re going to represent an entity that is one of two types. The types might have some attributes in common but also some type-specific attributes. In Typescript, you can represent types with both shared and type-specific attributes as a discriminated union. Let’s look at how to model that relationship in a database.

The Typescript Discriminated Union

For my software development team, we wanted to represent a listing that is either just an asset or a pack of assets. All listings — both asset listings and pack listings — share some attributes like name, price, and the listing user ID. An asset listing has some specific attributes like the original ID. Similarly, a pack listing has specific attributes like pack entries and the discount applied to the pack items.

We can easily represent the listing of type asset or pack with a discriminated union in Typescript:

type Listing = Asset | Pack

type ListingAttributes = {
	name: string;
	price: number;
	listingUserId: string;
}

type Asset = ListingAttributes & {
	type: "asset",
	assetDetails: {
		originalId: number,
	}
}

type Pack = ListingAttributes & {
	type: "pack",
	packDetails: {
		entries: Array
		discount: number
	}
}

If the type of Listing equals “asset,” we know that the listing is an asset listing and can access asset details like the original ID of the asset. Otherwise, if the type of Listing is “pack,” we know that the listing is a pack. That means we can access the pack details such as the pack entries and discount.

Database Model for the Discriminated Union

To model the above relationship in the database, we began by creating a Listing table. For starters, the Listing table has columns name, price, the listing user ID, and type.

If the type is “asset”, the Listing needs to have additional attributes such as the original ID. To support this, we created an Asset table and added an “assetId” foreign key to the Listing table.

Similarly, if the type is “pack”, the Listing table needs to support additional pack attributes like the pack entries and discount. We added a Pack table to support the pack attributes and added a “packId” foreign key on the Listing table. We also added a PackToListing table to represent the entries in a pack. The PackToListing join table has a “packId” foreign key to pack and an “assetId” foreign key for the entries in the pack.

We are now able to specify if a listing is of type “asset” or “pack” and provide additional asset or pack attributes. This model has all the fields we need to represent a listing. However, it’s still missing some constraints. A listing must contain asset attributes (and must not contain pack attributes) if the listing is of type “asset.” Similarly, a listing of type “pack” must contain pack attributes but not asset attributes.

You can enforce this via a database check constraint on the Listing table. We want to add a constraint enforcing that:

• If the listing type is of type asset, the listing includes asset attributes (the “assetListingId” foreign key to the Asset table is not null) but does not contain pack attributes (the “packListingId” is null); OR
• If the listing type is of type pack, the listing includes pack attributes (the “packListingId” foreign key to the Pack table is not null) but does not contain asset attributes (the “assetListingId” is null).

You can represent this constraint in SQL as:

ALTER TABLE 
  "Listing" 
ADD 
  CONSTRAINT listing_references_correct_record_for_type CHECK (
    (
      "Listing"."listingType" = 'pack' 
      AND "Listing"."packListingId" IS NOT NULL 
      AND "Listing"."assetListingId" IS NULL
    ) 
    OR (
      "Listing"."listingType" = 'asset' 
      AND "Listing"."assetListingId" IS NOT NULL 
      AND "Listing"."packListingId" IS NULL
    )
  );

Bringing It All Together

This constraint assures us that the listing returned from the database will either be of type “asset” with all the asset attributes or type “pack” with all the pack attributes.

The database representation of a listing now matches up with the Listing discriminated union type.