Hi All,
I am asked to make a database app for Gardening. All plants will need a column/field for full sun, medium sun and shade. Is it a good idea to make a string field with 5 charaters which can contain ie F or F,M or M,S or F,M,S for seaching and filtering? I use Firebird
My Clarion suggestion - BYTE field with simple bitwise operators. My plant suggestion - consider more than 3 sun options.
Since you area using a ârealâ database, why wouldnât you have a child file with suntypes. You can then create a view that joins the suntypes and filter to your heartâs content.
Thatâs the better way. Gives you all sorts of sub type options and the same concept can be used for other nuances that will inevitably crop up (pun intended)
Thx all, A view it will be:) Soil and other âchildâ options can be integrated too, as mentioned.
I suggest you closely consider how your SunTypes child table will relate to Plants. Difficult for me to imagine 1:M as has been implied.
I think itâs a human categorization thing. Clearly it is really a range. If we were doing pH I would suggest a low and a high rather than put in that it was happy at 5.5, 6.0, 6.5 and 7. But soil types I probably go with human-happy ranges like heavy clay, sandy loam, etc.
Add in the fact that plants often thrive in more than one of these. Same for light levels if there is such a thing as âhuman happy light.â
I think a matrix would be useful, because a plant might be happy in a low pH at warm temps, and also be happy at a neutral ph at a warm and cold temp.
Iâd suggest a couple of tables for the matrix. First (parent) is the planting instruction source, second would be planting instructions for the plant.
Then use something like StrPos( Loc:IsolatedPlantingInstructionWord, Mat:Keywords,True) to scan for pertinent words in the second table, in order to normalise the planting instructions from different sources. Even that would be a difficult, because one planting instruction source (ie seed manufacturer), may have more than one person providing the planting instruction and so variability could be introduced. Fortunately some words are vague which allows some leeway, so warm in one country could mean one temp range, but in another country another temp range, so the source of the planting instructions can be factored in.
One question is just how difficult do you want to make this in order to get close to perfection?
I assume a longtitide and latitude or environment type would also be included, like termperate, continental, if using something like the Köppen climate classification - Wikipedia .
That is the question for the designer. Does Claude or ChatGPT get to decide?
I used to use abbreviations that had a table that would lookup the full description. I donât do that anymore because while it saves some room, itâs minimal with the data sizes we have now. I would go with about a 20 character field with the full description, then a simple lookup to a small table.
Hi all,
For searching in need columns like
light:
full sun, medium sun and shade
Soil:
sand, loam, clay, stony peat
Moisture:
moist
dry, moderately moist, moist, variable, wet
Hight:
Color:
Evergreen:
Etc.
Maybe i could use a wizard to ask these questions sequentially
This is one of those things that always happens. You get an âeasyâ project wonât take much time⊠the more you think about it and try and actually define what you need to keep and the possibilities the bigger it gets.
Just resign yourself to the fact that youâre going to have 50 odd tables with many relationships. It always happens.
Just think about the plants and the attribute each could have. Then search the net for DB definitions or DB Models for the same thing. They almost certainly exist.
Good luck 
Hi Ray,
I mostly agree, but there are some circumstances where the code is better:
As an example of the first, there is a government program here that has variously been called;
mother baby nutrition supplement
prenatal infant nutrition supplement
prenatal and early childhood nutrition supplement
There are a bunch of reports the people who get only that program have to be excluded from. Using the code nothing else needs to change when the program name changes. If the name were stored other things would have to change.
Jon
For landscape designers, I think you will usually find at least 5 levels. For instance, check websites like Proven Winners (6 levels), Monrovia (5 levels). Because these are on a continuum and would require a change in definition to add or subtract, my habit has always been to use a BYTE or SMALLINT with bitwise operators and Clarion equates instead of a separate tables. The plant to light relationship is clearly M:M, which nicely translates to multiple checkbox controls, while still allowing fast, easy search conditions. Using child tables requires two tables for the M:M and also an attribute for light level in the Light table to go along with the description.
I believe much of the same case can be made for your other plant attributes such as soil, moisture etc.
Or one table for edits, with 50 alias files for lookups. This way all the keywords can be kept in one table, and each alias file could be filtered to limit the options.
This site has a layout of attributes which you might find useful.
Didnât think full sun could be a light choice in the UK. 
Well it depends on what sort of light you are after.
This is UV light around the planet at different times of the year and hour.
Yeah, I get that. Had the same thing with our video rental software (remember those days). The âTypeâ code was 3 characters, and the associated description, price, etc., were in a related record. It was good because you could change the description and price, and not worry about changing all the records for the associated inventory items. Every situation is different.
I recently gave instructions to Rocket.New to create what I thought would be a simple 2 or 3 page Flutter app. It came up with 10 pages that it thought I would need for the app, and it was right! (Great program by the way!)