I try to set up categories down to level 5. Have tried a few approaches but each has its drawbacks. Non primary fields can not be linked.
It should be structured for the user to be able to remember some common categories. But at level 5 the list will be very long.
Has anybody a recommendation on a table structure and how to link them.
If we don’t limit to five it reminds me of a structure for file-systems.
In one of my databases on art I use a text as Id for Category, so I can directly type or select an Id like oil17 or drw20 for paintings in oil / drawing from 17. / 20. century, or retrieve a longer description for other users. Another column tells in wich “folder” this Category-Id belongs (like fine-art/drawings/), as this can vary, because of topics of exhibitions.
.
What is most efficient depends very much on your actual use of this Category-Id and the size of the database.
Thanks for unblocking my brain. My solution is:
5 Tables each with 3 Fields (Unique integer, Integer as link to the next higher level, Name of the category)
On my form, using the Form-navigator i place one more sub-form in the form and then in the sub-form in a cascading manner.
Each form/sub-form gets a table-control with the 3 Fields.
Then in form-navigator right-click on each sub-form in turn, choose Properties. Here you can link the form to the right table and define “Link Master Field” and “Link Slave Field”
Voila, each table can hold 10 categories. On Level one the ID is “1”, Level two “10” Level three “100” . . . and so on.
When I select one entry, only the correspondent entries are shown in the next lower level.