CODEWITHSUNDEEP
django
Dmitry Shevchenko
Dmitry Shevchenko

Reputation: 32424

arbitrary typed data in django model

I have a model, say, Item. I want to store arbitrary amount of attributes on it, like title, description, release_date. And i want them to be not just strings but have python type, so string, boolean, datetime etc.

What are my options here? EAV pattern with separate name-value table won't work because of the same DB type across all values. JSONField can probably help, but it doesn't know about datetime, for example. Also i was looking at PickeField, it fits perfectly, but i'm a bit concerned about performance.

Upvotes: 1

Views: 557

Answers (1)

davidfischer
davidfischer

Reputation: 449

You have a couple of options and none of them are great. Some of them have been discussed before on Stack Overflow.

Firstly, as you suggested, you have the entity-attribute-value design pattern.

  • You can add DB type checking by having a table for VARCHARs and one for INTs and one for BOOLEANs and so on.
  • EAV makes selects very painful. You have to query a number of tables to actually get an object and if you have to use values from the EAV table in the lookup you will run into performance issues as the size increases.
  • In general, however, EAV should really only be used for very sparse data where another option simply does not work.
  • There is a Django package for this on PyPI, but I haven't used it.
  • I have seen some pretty large scale commercial products that use this approach when a lot of flexibility is absolutely required

A slightly better approach is to have a table whose schema changes and a metadata table that describes that table. For dense data where most items have most of the attributes, this has a lot of advantages over EAV. This approach is sometimes called dynamic tables or dynamic rows.

  • INSERTs, UPDATEs and DELETEs are much faster since everything is in 1-2 tables
  • Type checking and potentially constraints can be added
  • However, this approach leaves a very complex database that can be harder to work with
  • I don't know any way that Django would use its ORM with this kind of database since your models would be changing on the fly.
  • You are altering your database with ALTER TABLE on the fly. You better be very careful with your transactions

A good approach if you don't need to perform lookups based on these dynamic attributes is to store dynamic data in a JSONField or better yet a schema validated XMLField. However, lookups will be painful if you have to lookup based on a dynamic attribute that is part of your JSON or XML.

The best approach depends on how sparse your data is and how you'll be looking up that data. Also, a very good question to ask is if you absolutely need this flexibility. I've worked on some projects where we decided we needed EAV but since the project went into production attributes are rarely added and rarely removed so we got all the disadvantages and none of the boons.

Upvotes: 4

Related Questions