Storing and Accessing a Range of Numbers / Dates in MySQL

Question

I need to store a range of numeric or datetime values in MySQL, preferably in a single column. Unfortunately, as there are no real array or set data-types in MySQL, likewise it seems that there is no range data-type, so I'm a bit at an impasse here, hoping to come up with something smart.

Common use cases for a range would be e.g. storing the start and end times of an event, or the minimum and maximum prices of a given product.

In my case, I simply need to store the year(s) a book was written. In some cases, there is ambiguity and the year I have on record may be e.g. 810-820. Of course one way to go would be to have separate year_min and year_max columns, and then have identical data stored in both columns in case there is no variance.

Yet only a fraction of the entries I have would actually need to have such a range stored, and I'd much love to just query a simple BETWEEN 750 AND 850 for example -- and avoid both having a WHERE hit on two columns, as well as the redundant duplication of data in 98% of the cases.

What's your recommended approach? Any best practice tips? I know how to tune up decent two-column queries. I'm hoping there's another way to go about this... (And no, I'm not likely to switch to PostgreSQL just to have the benefit of their range types.)

Answer 1

I would recommend going with a two column solution, despite that it is not that sexy or clever. Suppose you implement this with one column. Then your database becomes non relational, because a given record and column now points to multiple values (year_min and year_max). So even though your schema might appear tidy, you might lose that benefit in the form of more difficult queries.