1

My example might be slightly contrived, because I've modified it so that the project isn't recognizable by my employer. I'm a newer developer at a very small company.

We have an object - Posts - that has a number of defined attributes (title, tags, content) and an unknown number of unknown attributes. These attributes are not required, but providing them helps later in the system.

These can be things like ip_address, user agent, crosspost, user_account_age, etc. These are not known and new ones can be added by the system at any time. Basically, if we suddenly want to collect a new attribute when a post is created, we want to be able to do so. The attributes are configured by the end user, not the developer. In this example, it'd be by the person writing the post, not the person hosting the forum.

How can I model this in Django? The custom attributes need to be searchable (ie. find all posts where the user account is less than a month old; find all posts that have been cross posted).

The defined attributes are easy enough to model. It's the optional custom ones that I can't figure out how to model or how to do it efficiently.

My first thought, from a database perspective, was to just have a key/value table where it'd have three columns: PostId, key, value. This seems inefficient though as everything would need to be a string because I can't declare a field as an IP address, integer, etc because the attribute could be anything. The query to join these to the posts would be pretty simple though. I'd just need to match on PostId.

Is there a better way to handle this?

The back end database can be anything, though we currently use a combination of postgres, mysql and redis. If there is another tool that would help model this more appropriately, though, we can adopt that.

  • 3
    If attributes have to be configured by an end user, EAV like your key/value table is probably your best choice. Just consider to add also some metadata (for example, for describing the data type of each value). – Doc Brown May 30 '18 at 16:01
  • @DocBrown An EAV is simple to set up, but doesn't perform very well... – btilly May 30 '18 at 17:14
  • Sounds like a document database would be ideal for this situation, particularly since they are more efficient at searching than normal RDBMS with JSON field support bolted on. – Berin Loritsch May 30 '18 at 17:28
  • @BerinLoritsch Actually RDBMS systems have better reliable performance than document databases. See percona.com/live/e17/sites/default/files/slides/…. – btilly May 30 '18 at 19:57
  • @btilly, I'll have to check that out when I get home. I'm having difficulty downloading the PDF at work. However, I will say that MongoDB (mentioned in the file name) is the last document database I reach for. Might not be a fair comparison. – Berin Loritsch May 30 '18 at 20:26
3

Django can store JSON directly in the database with JSONField. It is supported in some databases only, but https://docs.djangoproject.com/en/dev/ref/contrib/postgres/fields/#jsonfield and http://django-mysql.readthedocs.io/en/latest/model_fields/json_field.html show that it both MySQL and PostgreSQL are on the list where it is.

I don't know about MySQL, but I do know that in PostgreSQL not only can you search for a particular value inside of JSON, but you can index it as well. Furthermore https://www.postgresql.org/docs/8.0/static/indexes-partial.html provides an excellent option for having indexes only on the rows that actually have a particular key.

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.