3

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?

5

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.

  • Can I still achieve normalization / data integrity, by say creating an operating system table that forces and linking that from the operatiing_system key/value in the JSONB column? or would I just have to store the text, that would at least give me control over the values though – greyfox Sep 7 '15 at 16:13
  • @greyfox As I noted above, FK constraints cannot be the values of json keys. You can use CHECK constraints though. Or store that particular info in a normal column. – Craig Ringer Sep 7 '15 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.