Model with variable number of "properties" of different types.

Question

I would like to have a table of microscope slides, where on top of the basic id, name,date_created would each slide also have a number of editable "properties".

Imagine one slide having

• tumor_type
• dye_type
• description
• date_injected

And some other slide could have for example:

• patient_name
• treated_since
• description

I can't store all these in the slides table because I would like users to be able to chose used properties or add new ones if they wish to. Each property should also have a type, since some are integers, some text and some for example date and time and I would love to render a widget for them to simplify editing these values.

I planned to use MySQL, then I was pointed to use use PostgreSQL and its hstore, but that feels like a borderline experimental feature to me. Any suggestion how should I go about it?

This is what I came up with but it doesn't feel right to me: +-UML representation of what I am trying to explain here :)

Also all this will end up in Django (Python) if it makes any difference.

• András Váczi: PostgreSQL's hstore is far from being experimental, AFAIK.
• nana: Alright, I would also like to recycle already defined properties.
• Craig Ringer: psycopg2, the preferred PostgreSQL driver for Python, has hstore support; see initd.org/psycopg/docs/extras.html

Answer 1

hstore is ideal for this job, and isn't in any way experimental; it's been a part of PostgreSQL since 8.3 and is way easier to use in 9.1 and above thanks to CREATE EXTENSION.

The only real issue with hstore is that not all client drivers support converting hstore values to native maps or dictionaries, so you might need to get the properties for an entry as a table using the hstore sql function each, effectively converting the hstore to subtable result.

The only real alternatives I see are:

• A simple master-detail table in EAV style. That'll work fine, it's just a bit annoying to query, you'll be using a lot of string_agg or array_agg calls and it's lots slower than hstore. This approach is completely portable across database systems.

• Storing the properties as xml, which can be easier to parse from some applications and is sort-of indexable using functional indexes on xpath expressions. This approach is somewhat portable across database systems, in that it should work with any SQL/XML database.

• Storing the properties as json, which is easy to consume from most applications but is currently non-indexable and pretty opaque to SQL at the moment (expect improvements in future Pg versions). This approach is somewhat PostgreSQL-specific, but you can use a text/memo/clob field to store json in any database.

As for re-using properties that're already defined, that'a a simple matter of maintaining a table of known property names. If you want you can generate it on the fly using SELECT DISTINCT skeys(properties) FROM thetable; queries on the properties hstores, but that'll be quite slow so I'd recommend maintaining a separate table.