Ygthkb

What type of postprocessing gives the effect of people standing out

Multiplication via squaring and addition

Equivalent to "source" in OpenBSD?

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

Must a tritone substitution use a dominant seventh chord?

How to tighten battery clamp?

When was drinking water recognized as crucial in marathon running?

How to add multiple differently colored borders around a node?

Is there any relevance to Thor getting his hair cut other than comedic value?

chrony vs. systemd-timesyncd – What are the differences and use cases as NTP clients?

What is the difference between throw e and throw new Exception(e)?

Contradiction with Banach Fixed Point Theorem

How to properly claim credit for peer review?

What is better: yes / no radio, or simple checkbox?

Why zero tolerance on nudity in space?

I can't die. Who am I?

Is there a frame of reference in which I was born before I was conceived?

Borrowing Characters

Can I become debt free or should I file for bankruptcy? How do I manage my debt and finances?

How to count occurrences of Friday 13th

Where is this triangular-shaped space station from?

Hacker Rank: Array left rotation

Where was Karl Mordo in Infinity War?

Why do members of Congress in committee hearings ask witnesses the same question multiple times?

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 8 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 8 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 8 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 8 mins ago

the-a-trainthe-a-train

1063

share

asked 8 mins ago

the-a-trainthe-a-train

1063

asked 8 mins ago

the-a-trainthe-a-train

1063

asked 8 mins ago

the-a-trainthe-a-train

1063