in reply to OT - SQL choosing a schema for index tables
I see immediate problems with table-names like tree-flowering. Here would be my rules of thumb for designing such a database.
(1) Create a table for each “thing.” Well, in this case, the only “thing” is a tree. Any tree, “flowering” or not. Each tree record should have a primary key. Let’s call it tree_id.
(2) Create other table(s) for “attributes.” Let’s say that each of the fields you listed (usage, origin, flowering, etc.) are all multi-valued attributes, each of which can have only one of a set of possible values chosen from a list. In this case, each one would be in its own field as you have now done.
(3) Create tables to contain lookup-lists for each attribute. A table that contains, for example, all possible values for flowering.
(4) Use referential integrity rules and foreign keys to ensure consistency. For instance, a rule that specifies that flowering must contain a value from the lookup table, and that, should the value of the lookup be changed, the change will “cascade” to all occurrences of the former value. Also, that attribute values are unique and are NOT NULL.
(5) Use efficiencies like IN('spring', 'fall') to simplify queries. ‘Nuff said.
The actual SQL schema is left as an exercise for the reader.