CODEWITHSUNDEEP
mysqldatabasedatabase-design
camille
camille

Reputation: 328

How to keep range values in database

To check a patient's status in a medical test(high, low, average), I have to keep range values for the tests in a table

For a test there can be several ranges.

| range     | status  |
|-----------|---------|
| 0 - 100   | Low     |
| 101- 200  | Average |
| 201 - 300 | High    |

For another test, ranges can be different according to the gender

| range     | Gender | status  |
|-----------|--------|---------|
| 0 - 100   | Male   | Low     |
| 0 - 105   | Female | Low     |
| 100- 200  | Male   | Average |
| 105 - 210 | Female | Average |
| 200 - 300 | Male   | High    |
| 210 - 300 | Female | High    |

There can be range data as age, gender, status as well.

How can I map the ranges in database which enables to check which range user is in for a given test result. As an example For a Sugar Test a male user may have a result 45. I need whether the test value is high, low or average.

My initial table solution is shown below. I don't think this is a good solution.

| test | min_age | max_age | range_upper | range_lower | Gender | status  |
|------|---------|---------|-------------|-------------|--------|---------|
| 1    | 1       | 10      | 0           | 100         | Male   | Low     |
| 1    | 1       | 10      | 100         | 200         | Male   | Average |
| 1    | 1       | 10      | 200         | 300         | Male   | High    |
| 1    | 1       | 10      | 0           | 100         | Female | Low     |
| 1    | 1       | 10      | 100         | 200         | Female | Average |
| 1    | 1       | 10      | 200         | 300         | Female | High    |

Please sorry for my English.

Upvotes: 1

Views: 1088

Answers (1)

serge
serge

Reputation: 1022

Why do not use range reference instead of pair of values?

Table status_ranges

| id | range_min | range_max | status  |
|----|-----------|-----------|---------|
|  1 |         0 |       100 | Low     |
|  2 |       101 |       200 | Average |
|  3 |       201 |       300 | High    |

Table of tests

| test | value_1 | gender | id_status_of_value_1 | . . . 
|------|---------|--------|----------------------|-----
| 1    |      10 |  Male  |                   1  | . . .
| 1    |     120 |  Male  |                   2  |
| 1    |     235 |  Male  |                   3  |
| . . .

Upvotes: 1

Related Questions