What is the recommended schema design for dynamic dates in Django for PostgreSQL?

[Radek Svarz]

Hi all,

we have a Django app focusing on timeline evolution visualization. There we have conceptually the relationship of:

1 Item with 1 or more Lifecycles (more for versioning purposes)

1 Lifecycle has 0..n Milestones

1 Milestone is a date stored as a string in form YYYY-MM-DD or a special tag "today", which means daily changing date (dynamic - the date was not stated, but until today is some state valid - if today is smaller then the next milestone).

The character of the data is that there are very diverse interpretations of milestones and phases in between them. Also the amount of milestones is diverse. However there seam to be used a maximum of 7 milestones. The characteristics of lifecycle records can be grouped (same amount of milestones with the same meanings).

Overall current implementation works fine, however we have issues with reporting questions such as:

Which items will hit milestones of certain characteristics in December 2015?

What is the best practise? 

Django code and further long details are @ SO: http://stackoverflow.com/questions/33167079/what-is-the-recommended-schema-design-for-dynamic-dates-in-django-for-postgresql 

Thanks, Radek

[Erik Cederstrand]

> Den 20. okt. 2015 kl. 14.09 skrev Radek Svarz <radek...@gmail.com>:
>
> [...]

> 1 Milestone is a date stored as a string in form YYYY-MM-DD or a special tag "today", which means daily changing date (dynamic - the date was not stated, but until today is some state valid - if today is smaller then the next milestone).

> [...]

> Overall current implementation works fine, however we have issues with reporting questions such as:
>
> Which items will hit milestones of certain characteristics in December 2015?

If you want the dates to be searchable, then don't store them in a Charfield. Store them in a DateField instead. In fact, always store dates in a DateField.

In any case I would create a separate "Milestone" model with an m2m relation to Lifecycle. Put your "today" logic for milestones in this model, instead of using N duplicate date fields. Either use null=True to denote your concept of "today", or add a separate Boolean field "is_today" on the "Milestone" model.

Erik

[Erik Cederstrand]

> Den 20. okt. 2015 kl. 14.09 skrev Radek Svarz <radek...@gmail.com>:
>
> [...]

> Django code and further long details are @ SO: http://stackoverflow.com/questions/33167079/what-is-the-recommended-schema-design-for-dynamic-dates-in-django-for-postgresql

Regarding your need for a way to denote "natural language" relative dates, take a look at https://pypi.python.org/pypi/dateparser. You could either use that directly or build on top of it.

Erik

[Radek Svarz]

Hi Erik,

thanks for m2m suggestion.

What do you think about using suggested view in DB? (@SO) (i.e. the DB would do the conversion on the fly)

Radek

[Erik Cederstrand]

> Den 20. okt. 2015 kl. 18.09 skrev Radek Svarz <radek...@gmail.com>:
>
> Hi Erik,
>
> thanks for m2m suggestion.
>
> What do you think about using suggested view in DB? (@SO) (i.e. the DB would do the conversion on the fly)

In general, I think of DB views as either a performance optimization or a way to store business logic. I like to keep my business logic in one place (Python code) and hold off on performance optimizations until they are identified as necessary, so I would stay away from views until you have no other reasonable options.

Erik