SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Suboptimal Solutions

Suboptimal Solutions

Let’s learn methods to avoid EAV that can be used where inheritance methods cannot be used.

We'll cover the following...

There are two more solutions to model the subtypes that we are going to discuss in this lesson. These are:

  • Semistructured data
  • Post-processing

Semistructured data

If we have many subtypes or if we have to support new attributes frequently, we can add a BLOBbinary large object column to store data in a format, such as XML or JSON, which encodes both the attribute names and their values. All database brands support the BLOB data type, which we can use to store any binary data. Martin Fowler calls this pattern the Serialized LOB.

Press + to interact
CREATE TABLE Issues (
  issue_id SERIAL         PRIMARY KEY,
  reported_by             BIGINT UNSIGNED NOT NULL,
  product_id              BIGINT UNSIGNED,
  priority                VARCHAR(20),
  version_resolved        VARCHAR(20),
  status                  VARCHAR(20),
  issue_type              VARCHAR(10), -- BUG or FEATURE
  attributes              TEXT NOT NULL, -- all dynamic attributes for the row
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
...