Postgresql – A good use case for Postgres JSONB

database-designeavnosqlpostgresqlpostgresql-9.4

I am looking to create an Asset Management System to track assets (laptops/desktops, monitors, keyboards, mice, bags, software keys, etc), basically anything that can be assigned to an employee. My issue is, each type needs to track different attributes. We would track a different set of properties / attributes for computers than say a monitor. I've read about EAV pattern (or anti pattern from what most people say) and it seems like that should be avoided by the plague. Creating a single table with all the columns just seems ridiculous, and creating a new table for each new type that comes along doesn't seem optimal. I recently read about JSONB in Postgres 9.4. Storing a JSON object that can be handled by the application seems like a decent compromise.

Is this a good use case for JSONB? Or does EAV make sense? Or making a giant table, or creating a table per type?

Best Answer

This is indeed a reasonable case for storing object-like or key/value data, and representing it as JSON in jsonb fields in PostgreSQL is a reasonable way to do that.

In general it's time to consider hstore, xml, jsonb, etc when you're starting to look at alternatives like EAV or wide tables where the app adds columns dynamically. jsonb is basically the new hstore with a more standard way of representing its data and with nesting capabilities, so it's preferable most of the time.

Static tables for different kinds of thing, with parent tables for more general categories of thing, still make sense if the things being tracked are (a) fairly static and not user-defined, and (b) not excessively numerous. That does not sound like the case here.

I still recommend keeping attributes as regular columns where:

  • They form part of a referential relationship (is-a, has-a, contains, is-contained-by, owns, is-owned-by, etc);
  • They are a natural key where a UNIQUE constraint makes sense;
  • They are filtered on or otherwise queried in the large majority of queries

You can define a unique constraint - or rather, unique index - over an expression that queries the json field. It's just often cleaner to have them separate. Similarly expression indexes can help with oft-queried fields you want to keep part of the json object. It's really the first point that's the most important, as RI constraints cannot reference the value of a json object key.

Related Question