I would not put these into different TABLES, just different records of the same table with self-join to itself with a "parent_id" field pointing to the record that is the parent. That's storage, that's ease of search. Display in a tree? I believe there may be threads in this forum (or others) discussing display
(genealogy, pedigree, organizational charting), such as:
not very clear I'm afraid - we don't know how you code the single photo.
However, you could use your diagram as a fixed picture with buttons over the boxes, doing searches.
If you look at your diagram, you can see horizontal levels that can be a key.
Level 1 is INature, 1.
Level 2 is Plants, 1 or Animals, 2.
Level 3 is Mosses, 1, or Tracheo...thing, 2.
and so on.
This allows building a key like 12221 for conifers, when you press that button.
Searching for it in your picture keys should give you the results, provided you classified them correctly.
Thanks Beverly and Siplus. Beverly, you suggested placing all of the information into one table, with many records. The reason that I spread the data out into many tables is because I was under the impression that similar kinds of data should be placed within the same table. So in my situation, should I make a massive table with very different kinds of info, such as flowering plant morphology and invertebrate characteristics in the same table. For my Angiosperm table, I have about one hundred families and scores of morphological character states. It would seem impractical to include information about the rest of the plant and animal kingdom. The kinds of information also changes within a lineages hierarchy: characters at the family level aren't the same as those at the genera or species levels. Anyway, you have given me something to think about. Hopefully some more folk will weight in on how i should organize the data. Thanks!
Siplus, I like your idea about a fixed picture, but I also want a user to be able to follow nature's natural structure in attempting to identify a specific organism. So are you also suggesting not to have the data arranged in structured tables? Thanks.
Yes one table!
Create a record which has an auto-enter id (unique) let's call it TreeID_pk
The Classification field is "Kingdom"
The Scientific name is "Plantae"
The Common name is "Plants"
... any other significant fields as needed ...
TreeID_fk is "0" (because this is the top level! but normally every other record has a parent relating to TreeID_pk of the parent)
This Parent has the child(ren):
Classification = "Subkingdom"
Scientific name = ...
Common name = ...
TreeID_fk (which is the one assigned to "kingdom", above)
... etc. on down the line for each division for a particular plant.
Because there is a relationship between ANY record and its parent, it really also has a relationship to its children. This can be used to navigate as needed.
You aren't duplicating any of the divisions, you just relate however needed. There can be one or many children for each record.
Again, the trick is getting a "chart". I used a method to print list view and placed calculated spacing so that you could see a hierarchical list:
That's not a "chart" but it is easy to see.
BTW, I did not do this with plants or animals, but other sets of data that had a hierarchical structure. I have done this!
Does that make sense to you? This IS similar data and it would not make sense to break down into more than one table. If you have details (characteristics) that make sense to place in other tables, sure (maybe).
Getting to the data that has to be "tunneled back" for reports probably is not what you want. But think more on what I'm saying.
I'd use only 2 tables, plants and animals, in order to avoid too many useless fields.
- The first one for the hierarchy (or classification) - as Beverly pointed out. See also Infinitive Hierarchies with FileMaker by Matt Petrowsky (Unlimited Infinite Hierarchies - ISO FileMaker Magazine). The same principles are also applied in the following online catalogs driven by FM: CLICAPS Topics, or Physical Properties Sources Index (PPSI). You can choose one of the topics and navigate through the hierarchy.
- The second one is a general property table applying the key-value model. This avoids to define any specific fields for a given category (animals or plants), and is open for extension. Also, it allows to find any specific value regardless of position in the hierarchy and field descriptor. See e.g. Fmk2013 datenmodelle krambrich-brändle (rev)
Bev, the second is only the AV part (= key-value) of EAV.
What the entities E are, needs to be defined:
- the items in the hierarchy table - sure so
- or the pictures that are assigned to the items in the hierarchy table - also these could have AVs (a varying number of properties and their values)
- or both
Classification Item (E) <---> Properties (AV)
<---> Pictures (E) <---> Picture Properties (AV)
Thanks Siplus. You mention just two tables to avoid too many empty fields. But the same situation occurs at many other levels of the taxonomic hierarchy. The question, to me, is still whether to structure the data around tables, records, or probably both.
Thanks Beverly. It still seems to be that using tables (in addition to records within a larger grouping) is preferable as there would be too many empty fields when using a super table. Even within Angiosperms, I have more than 100 families and scores of fields (for characters) each with numerous character states (value list checks). Perhaps using your records only approach one could use different layouts for different groups of organisms. I am new at this so I will try to keep an open mind...don't what to go down the wrong pathway at this point of the data acquisition. Jeff
This is why Herr Braendle says to use a second table for the "characteristics". You are relating back however you need.
And I concur.
Did you follow the links he provided? It's web pages for display, but based on what we are both saying for the storage of the data of this type.