Sfrgttk

How do ISS astronauts "get their stripes"?

Find the next monthly expiration date

I encountered my boss during an on-site interview at another company. Should I bring it up when seeing him next time?

Is divide-by-zero a security vulnerability?

If nine coins are tossed, what is the probability that the number of heads is even?

Skis versus snow shoes - when to choose which for travelling the backcountry?

How to approximate rolls for potions of healing using only d6's?

Where is this triangular-shaped space station from?

Do authors have to be politically correct in article-writing?

Multiplication via squaring and addition

What am I? I am in theaters and computer programs

Can you benefit from a Hammer of Thunderbolts’s Strength increase by holding it with something else than your hands?

What can I substitute for soda pop in a sweet pork recipe?

You'll find me clean when something is full

How to add multiple differently colored borders around a node?

Make me a metasequence

Must a tritone substitution use a dominant seventh chord?

What's the difference between a cart and a wagon?

Use comma instead of & in table

I can't die. Who am I?

Did 5.25" floppies undergo a change in magnetic coating?

How to avoid being sexist when trying to employ someone to function in a very sexist environment?

Avoiding unpacking an array when altering its dimension

Understanding Kramnik's play in game 1 of Candidates 2018

how to structure availability relationships

How to ensure that date-range queries involving multiple time zones are sargableIndexes involved in intervals collision detectionSchema/Structure to handle secondary computed dataRow for each date, or 31 columns?Best db setup to accomplish yes/no list of clients for daily callsStoring resource information efficientlyQuotation and invoice mysql tableNormalize repeated indefinite ongoing dates for an eventRemind customers of an event 7 days before the event based on a datePerformance - Database queries for range of dates

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.

mysql database-design

share

asked 5 mins ago

the-a-trainthe-a-train

1063

add a comment | 

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.

mysql database-design

share

asked 5 mins ago

the-a-trainthe-a-train

1063

add a comment | 

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.

mysql database-design

share

asked 5 mins ago

the-a-trainthe-a-train

1063

{

    'immediate': true

}

{

    'relative': {

        'period': '4',

        'unit': 'Weeks'

    }

}

{

    'date': '2019-03-05'

}

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

share

asked 5 mins ago

the-a-trainthe-a-train

1063

share

asked 5 mins ago

the-a-trainthe-a-train

1063

asked 5 mins ago

the-a-trainthe-a-train

1063

asked 5 mins ago

the-a-trainthe-a-train

1063