0

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.

  • Do you really want to shift the availability by one day tomorrow? And another day on the next day? – Rick James Mar 11 at 22:25
1

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;
  • Thanks for your feedback but I don't think you have understood my problem. – the-a-train Mar 5 at 5:24
  • @the-a-train - It is better to do the arithmetic before storing the data. And then don't hide it in JSON, rather have a simple DATE column. – Rick James Mar 11 at 22:25
  • But then I don't know whether the candidates availability is now (unemployed looking for a job), relative (needs to give 4 weeks notice at current job) or a specific date (currently in a contract that ends on a particular date). – the-a-train Mar 12 at 20:49

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.