Use case for hstore against multiple columns

Question

I'm having some troubles deciding on which approach to use.

I have several entity "types", let's call them A,B and C, who share a certain number of attributes (about 10-15). I created a table called ENTITIES, and a column for each of the common attributes.

A,B,C also have some (mostly)unique attributes (all boolean, can be 10 to 30 approx). I'm unsure what is the best approach to follow in modelling the tables:

1. Create a column in the ENTITIES table for each attribute, meaning that entity types that don't share that attribute will just have a null value.
2. Use separate tables for the unique attributes of each entity type, which is a bit harder to manage.
3. Use an hstore column, each entity will store its unique flags in this column.
4. ???

I'm inclined to use 3, but I'd like to know if there's a better solution.

Tennes

As an addendum to #2, and only because you're doing this in postgres, I'd add in using table inheritance. The perceived maintenance overhead incurred by having three tables plus a generic "parent entity" table is easily disregarded in favor of having an easily enforceable schema. That is, with an hstore or even a more generic entity-attribute value approach, you lose out on the native column typing mechanisms built in to the database. – Trisyllable

I didn't know Postgres supported inheritance, I'll look into it, thanks. – Tennes

Answer 1

(4) Inheritance

The cleanest style from a database-design point-of-view would probably be inheritance, like @yieldsfalsehood suggested in his comment. Here is an example with more information, code and links:
Select (retrieve) all records from multiple schemas using Postgres

The current implementation of inheritance in Postgres has a number of limitations, though. Among others, you cannot define a common foreign key constraints for all inheriting tables. Read the last chapter about caveats carefully.

(3) hstore, json (pg 9.2+) / jsonb (pg 9.4+)

A good alternative for lots of different or a changing set of attributes, especially since you can even have functional indices on attributes inside the column:

• unique index or constraint on hstore key
• Index for finding an element in a JSON array
• jsonb indexing in Postgres 9.4

EAV type of storage has its own set of advantages and disadvantages. This question on dba.SE provides a very good overview.

(1) One table with lots of columns

It's the simple, kind of brute-force alternative. Judging from your description, you would end up with around 100 columns, most of them boolean and most of them NULL most of the time. Add a column entity_id to mark the type. Enforcing constraints per type is a bit awkward with lots of columns. I wouldn't bother with too many constraints that might not be needed.

The maximum number of columns allowed is 1600. With most of the columns being NULL, this upper limit applies. As long as you keep it down to 100 - 200 columns, I wouldn't worry. NULL storage is very cheap in Postgres (basically 1 bit per column, but it's more complex than that.). That's only like 10 - 20 bytes extra per row. Contrary to what one might assume (!), most probably much smaller on disk than the hstore solution.

While such a table looks monstrous to the human eye, it is no problem for Postgres to handle. RDBMSes specialize in brute force. You might define a set of views (for each type of entity) on top of the base table with just the columns of interest and work with those where applicable. That's like the reverse approach of inheritance. But this way you can have common indexes and foreign keys etc. Not that bad. I might do that.

All that said, the decision is still yours. It all depends on the details of your requirements.

Perspicacious

Personally, I'd be against using inheritance because you cannot enforce uniqueness of a primary key or unique constraint across the parent and all its children. For details on Pg-specific modelling alternatives see dba.stackexchange.com/questions/27057/… and https://mcmap.net/q/1480511/-storing-dynamic-properties-of-objects-in-sql-duplicate/398670 – Earthaearthborn

Great answer, thank you. I might have gone with inheritance if it wasn't for the limitations you linked to. hstore seems to fit more my requirements, so I'll go with it. – Tennes

Answer 2

In my line of work, we have rapidly-changing requirements, and we rarely get downtime for proper schema upgrades. Having done both the big-record with lots on nulls and highly normalized (name,value), I've been thinking that it might be nice it have all the common attributes in proper columns, and the different/less common ones in a "hstore" or jsonb bucket for the rest.

Fincher