This is something I always feel I could do better - hence I am asking how others would approach this.
I am interested in how one would organise the following in a 3-tier model of data, business and user interface:
A record in a table (eg. booking) has a main type (MT) and a sub type (ST).
A MT has various STs eg.
Main type A) has STs of X and Y
Main type B) has STs of X and Z
Main type C) has STs of Y and Z
where X, Y and Z refer to text descriptions.
Table MT has a text description an an integer key
Table ST has an integer key, a foreign key (for the MT) and a text description
A: aaa, 1
B: bbb, 2
C: ccc, 3
in the sub types table there are the following (with ID, link to MT table and text which can be duplicated).
1, 1, xxx
2, 1, yyy
3, 2, xxx
4, 2, zzz
5, 3, yyy
6, 3, zzz
Now a booking is made
foo foo with MT of A and so for the ST only the xxx and yyy (with the keys of 1,2) should appear on the GUI. eg. In a combo to select
Now another booking
bar bar with an MT of C so for the ST only yyy and zzz (with the keys 5, 6) should appear on the GUI.
What would be a suitable 3-tier organisation for the GUI to 'fill' the combo with the allowed STs and for the resultant new record to be written back to the database ?