At first glance, it seems like the most appropriate thing to do would be to make the column storing the test result value nullable, and declare an additional column, perhaps an ENUM, for the out-of-range condition. For example
CREATE TABLE ...
...
result_value DECIMAL(6,3) DEFAULT NULL,
out_of_range ENUM('low','high') DEFAULT NULL,
...
When a test comes back with an out-of-range result, it would seem like you wouldn't want to store anything in the `result_value` column, because when it comes to averages or other statistics the low or high threshold value would completely skew your average... but when you take the AVG() of a data set that includes NULL the denominator used to calculate the average is the total number of not-NULL values... so AVG(5,NULL,NULL,10,NULL) would be 7.5.
Setting a flag that indicates that your result was, instead, out of range, would allow you to easily tally those values separately, and an ENUM column with a small number of possible values requires only 1 byte of storage per row... the "low" or "high" would actually be stored as the byte 0x01 or 0x02, with the corresponding labels stored in the table definition only.
When the minimum threshold is "5" the number "5" is not a meaningful number when it comes to analysis, so I would think that you wouldn't likely want to store that in the `result_value`. If you did, your analytic queries would have to go to extra steps to exclude those from calculations.
I would suggest that you do need a table that specifies the high and low boundaries for each test, because such a table could be used to constrain the data going into the results table via triggers, blocking the insertion of out-of-range data, in addition to being valuable information when doing analysis.
"But we already know those values." The database also needs to "know" those values, because subsequent analysis will be more straightforward if those values can be joined and understood by a query.
Also, I would suggest that if there is any possibility that the sensitivity, selectivity, granularity, precision, range, or whatever the proper term might be, of any test could change -- due to improved technology or whatever the reason, that you consider each test to actually be a 1 of N possible subtypes of a type of test (where initially, N is 1). If the possible range (or other property) of a result is different, then that's technically, arguably, a different test, even though it's measuring the same thing... and these are the kinds of things that are a small hassle now, and a much bigger hassle later.
needing to add new ones easily isn't a concern.
Famous last words. Speaking of bigger hassles later, that's generally not a good position to hold, even if it seems unshakable now.
A final word about a common design error I sometimes encounter: If you are using the FLOAT or DOUBLE data types, reconsider that decision, because they are imprecise. A DECIMAL column has a fixed precision and stores exactly the value you insert. Data stored in FLOAT and DOUBLE columns are stored as approximate values which can lead to problems with comparisons. This limitation is one of floating point arithmetic in general, and not of MySQL in particular; it is something with which you may already be familiar but I thought it worth mentioning.
No. This will cause maintenance problems: when you want to update the structure of Garden, then changes (adding a new column, removing an old column, changes to constraints, changes to triggers, whatever) will have to be propagated to every Garden_* table. It is also bad data modelling. You should aim to have one table per entity/relation in your system. Having multiple Garden tables would imply that they are fundamentally different.
I'd suggest a structure more like this:
Gardener
--------
ID (PK)
Garden_ID (FK to Garden.ID)
(other fields such as name, contact info, ...)
Garden
------
ID (PK)
(other fields such as name, garden address, ...)
Flower
------
ID (PK)
Type_ID (FK to FlowerTypes.ID)
Garden_ID (FK to Garden.ID)
(other fields)
FlowerType
----------
ID (PK)
name
(other fields)
The data for this schema could look like this:
Gardner
-------
ID | Name | Garden_ID
-----------|----------
1 | Bob | 100
2 | Sally | 101
Garden
------
ID | Name
---------
100| Big garden
101| Little garden
Flower
------
ID | Type_ID | Garden_ID
----|-----------|----------
1 | 200 | 100
2 | 201 | 100
3 | 202 | 101
Flower_type
-----------
ID | Name
-----------
200 | Lilly
201 | Hosta
202 | Rose
This will let a gardener be assigned to one garden. If you need more flexibility such that gardeners can be assigned to multiple gardens, and gardens can have multiple gardeners assigned to them, you'd need a separate garden assignment table, and remove the garden_id field from gardener:
garden_assignments
------------------
ID (PK)
garden_ID (FK to garden.ID)
gardener_ID (FK to gardener.ID)
Best Answer
You have the wrong table structure planned.
Tip: Naming a series of columns on a table with a sequential number (
foobar1,foobar2,foobar3) is often a sign of incorrect database design.Many-To-Many
You have children and you have vaccines as entities. Each should be represented by a table.
Furthermore, you have a relationship between those two entities. Each child can get a dose of zero, one, or more vaccines. Each vaccine may be given to zero, one, or more children. That means these two entities have a Many-To-Many relationship.
A Many-To-Many relationship is always a problem. The solution is always a third table that bridges the other two tables, representing the relationship between them. The bridging table always has at least two fields; as a child table, it has a foreign key to each of the parent tables.
In your case, the bridging table would be named something like
dose, tracking which kid got a dose of which vaccine. And in your case, the table would have a third field, the date when the dose was given.To present this data together, you perform a database join to extract various columns from the various tables where rows match up the foreign key on the child table (
dosage) to the primary key of each the parent tables (patient&vaccine).For more discussion of Many-To-Many, search for the canonical example: Books, Authors, and a bridging table named something like Authorship. Another example is employees being assigned to teams/projects. Yet another example is music albums where an album is categorized one or more genres, and each genre can have many albums assigned to it, as discussed in this Question.
Also search to learn more about how to separate such data into tables, a process known as database normalization.
This Answer addresses only the “history of administered dates” portion of your problem, not the scheduling.
Your problem of vaccination records is not simple. As a beginner, I suggest working on a simpler problem.