Mysql – how to structure availability relationships

database-designMySQL

I am building a recruitment database and need to structure 'current availability' data against candidates which can be one of three options – available now, a specific date (e.g. 5/3/2019) or a relative date (e.g. 4 weeks). When querying the database in my application the relative date will be calculated on the fly.

I am trying to work out the best way to store this data in a relational database. In my project I am using MySQL 5.7. I have tested and can see that storing this as JSON is possible e.g. the availability field in candidates table can be JSON with the following

{
    'immediate': true
}

or

{
    'relative': {
        'period': '4',
        'unit': 'Weeks'
    }
}

or

{
    'date': '2019-03-05'
}

the query can be something like this

select `candidates`.*, 
CASE
    WHEN availability->>'$.immediate' = 'true' THEN date(now())
    WHEN availability->>'$.date' THEN date(availability->>'$.date')
    WHEN availability->>'$.relative.unit' = 'week' THEN date(DATE_ADD(now(), INTERVAL availability->>'$.relative.period' WEEK))
    WHEN availability->>'$.relative.unit' = 'month' THEN date(DATE_ADD(now(), INTERVAL availability->>'$.relative.period' MONTH))
END as available from `candidates`
order by `available` desc

Although this works I am adverse to using JSON in a relational database so wondering how the same result could be achieved in other more relational ways.

Best Answer

IMO you overcomplicate things. Just add an available DATE field to the candidate table. Then queries will be simpler and more performant.

SELECT * FROM candidate WHERE available < NOW() ORDER BY available DESC;
Related Question