Self Expanding Categorisation
I hope someone can spare a little time to help me out on this - thank you very much in advance.
I would like to have two fields, one called "Category Name", and the other called "Parent Category". This is so that with two fields, I can get the whole hierarchy of categories and sub categories. I guess this is linked to normalisation. This is the backend database portion. On the layout, I would like to get a list view with an Excel Pivot Table sort of format - that the top most category is on the left column, and then it groups the next sub-category column to its right, and then that groups the next sub-category to its right so forth until there are no more sub categories. So I guess I am trying to expand a normalised form.
I've thought of the concept in SQL and it would be a parent (leftmost) column displaying values of "Category Name" generated from a WHERE criteria selecting those categories with empty "Parent Category" values, and then the next field would be a column generated from another WHERE criteria where "Parent Category" = the column on its left's value, and so on and so forth. Like a recursive column generation to expand out the data I have in the "Category Name" and "Parent Category" table which are linked logically already cause each record has a category name value with a value that reflects its parent category.
a) Which normal form is this type of normalisation found in?
b) How can I get my expanded form in list view and in table view?
c) Is there any way I can use drop down menus when I click on a table view cell just like Access?
d) Is there any way I can select the value list I set in the validation of a table view cell to quickly populate a cell?
e) Is there any way I can turn on autocomplete for table view cells just like Access and Excel where it would offer to complete the value with the existing values I typed in the column already?
Thank you very much for you time and help on this, I appreciate it very much!!
Updated Semi Answer (see image for table model):
In a brand new table, for list view, I would put 4 drop down fields. The first field would be called "Top Category" and its value would be either blank or a lookup from the "Parent Category". The second field would be called "Sub-Category 1". This is where the "secret" of the method is - there is a relationship linking "Parent Category" as FK from this table to the table containing my original PK "Parent Category" and "Category Name" - thus forming a parent-child table relationship, where the brand new table is the child. My "Sub-Category 1" drop down would then have its value list based on the parent table's "Category Name". And because the relationship ensures that only the results from the matching records of "Parent Categories" are churned up, the "Category Names" that come up will be the top most level when I set the FK "Parent Category" to blank.
Next I would do the same, for "Sub-Category 2", creating a relationship from "Sub-Category 1" to "Parent Category" of the parent table, and the value list for "Sub-Category 2" would be once again the "Category Name", and because of the relationship, it would churn up the relevant sub categories for a selected "Sub-Category 1".
The problem with this is that it is not recursive and limited to how many "Sub-Category x" fields I create. Is there any way to dynamically generate "Sub-Category x" fields such that there is always only one extra field for me to fill up - and once that is filled, another one would come up?
I still don't have a solution for table view and actually table view interests me the most.