In day to day use and operation, we strive for a certain degree of normalization of the data in our database. In reporting though, this normalization causes some friction: we often want the output to contain these duplicates of the data, for sake of row to row completeness. If we’re reporting on top-grossing films in the last decade, it’s easy enough to join the director table and list their name and year of birth. Collecting a set of properties for which we don’t know the names and quantity ahead of time is a little more challenging and interesting.
In our example, we’ll work with a schema that’s a little bit like an Entity–attribute–value model, but with strings for both the attribute and the value to keep things a little simpler. Of course, we’ll pick everyone’s favourite tool for bundling arbitrary data: JSON, and collect these properties into a single object.
Before we get into the actual SQL syntax, let’s describe the schema we’ll be working with:
CREATE TABLE item ( id SERIAL NOT NULL, description TEXT, PRIMARY KEY (id)) CREATE TABLE item_property ( id SERIAL NOT NULL, item_id INTEGER, label TEXT, value TEXT, PRIMARY KEY (id), CONSTRAINT uq_property_label UNIQUE (item_id, label), FOREIGN KEY(item_id) REFERENCES item (id))