3-Tier example required
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
so:
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 ?
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
so:
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 ?
If the subtypes xxx in 1,1,xxx and 3,2,xxx are the exact same, then you really should have another table.
key, main_type
1, aaa
2, bbb
3, ccc
key, sub_type
1, xxx
2, yyy
3, zzz
main_type_key, sub_type_key
1, 1
1, 2
2, 1
2, 3
3, 2
3, 3
Then the subtype combo box would just be a join on the three tables.
key, main_type
1, aaa
2, bbb
3, ccc
key, sub_type
1, xxx
2, yyy
3, zzz
main_type_key, sub_type_key
1, 1
1, 2
2, 1
2, 3
3, 2
3, 3
Then the subtype combo box would just be a join on the three tables.
ASKER
Thanks to both.
OK, maybe I wasn't too clear on an aspect. The xxx is the same text, but different sub types.
eg.
Think Accounting has an employee called John Smith and Planning has a different employee also called John Smith. One selects the dept. then the employee.
ps. I'm trying to reduce a more complex situation to a theoretical simple equivalent.
OK, maybe I wasn't too clear on an aspect. The xxx is the same text, but different sub types.
eg.
Think Accounting has an employee called John Smith and Planning has a different employee also called John Smith. One selects the dept. then the employee.
ps. I'm trying to reduce a more complex situation to a theoretical simple equivalent.
The "employee" should have some kind of employee id that uniquely identifies him and the tables should all use that.
I don't see where the challenge is. You populate the combobox with the names from the subtype table but have the id under the covers and you use the id everywhere.
I think I'm still missing something.
I don't see where the challenge is. You populate the combobox with the names from the subtype table but have the id under the covers and you use the id everywhere.
I think I'm still missing something.
Hi Andy,
I'm still fuzzy on what you're trying to describe.
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
Since "X" is a sub-type of "A" and "B", there are two rows in the ST table for "X", one for each related main type.
I'm not sure how the "John Smith" example applies. Data is data and if two STs happen to have the same text, so what? The "identity" of the ST is its primary key, not the associated data.
Kent
I'm still fuzzy on what you're trying to describe.
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
Since "X" is a sub-type of "A" and "B", there are two rows in the ST table for "X", one for each related main type.
I'm not sure how the "John Smith" example applies. Data is data and if two STs happen to have the same text, so what? The "identity" of the ST is its primary key, not the associated data.
Kent
ASKER
This is the critical part of my question:
I am interested in how one would organise the following in a 3-tier model of data, business and user interface:
...
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 ?
I am interested in how one would organise the following in a 3-tier model of data, business and user interface:
...
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 ?
I don't get the 3-tier part. You expose the info that should be exposed for whoever is viewing the combobox and use the unique ids in the background.
Data stored the database. UI just calls into the data model which queries the database and sends the list to the UI. I don't know what you mean by 'business' here.
Data stored the database. UI just calls into the data model which queries the database and sends the list to the UI. I don't know what you mean by 'business' here.
ASKER
My understanding of a 3 tier is the following:
data - handles the raw data.
UI - provides an interface the user can manipulate data without knowing anything about how and where the raw data is stored.
business - provides an interface where data is to be assembled into coherent objects for the UI.
I can think of a few ways to organise things in my given example - I am interested in hearing from others how they would organise it without me colouring their thoughts.
data - handles the raw data.
UI - provides an interface the user can manipulate data without knowing anything about how and where the raw data is stored.
business - provides an interface where data is to be assembled into coherent objects for the UI.
I can think of a few ways to organise things in my given example - I am interested in hearing from others how they would organise it without me colouring their thoughts.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. I appreciate the reasoning behind a 3 tier design - just I am coding all three tiers. Unfortunately one can pick up bad habits so this is like a revision excercise.
That is pretty much how I would have thought to be 'best'.
A couple of points I would like to discuss further.
When one changes the main type then different sub types become available. Assume the UI choices are both in combo boxs. How would a business object look to you?
(The data can be considered static for the duration, no requirement to keep querying the database.)
Would you have two - one for MT and one for ST, each essentially wrapping the underlying table.
Would you have just one that is used to fill both combos (eg. like a tree internally)
Linked with this at the UI level what are your thoughts about refilling the ST combo when the MT combo changes. The form both are on handles that, or the MT combo having a handle to the ST combo and calling that directly to refill ?
That is pretty much how I would have thought to be 'best'.
A couple of points I would like to discuss further.
When one changes the main type then different sub types become available. Assume the UI choices are both in combo boxs. How would a business object look to you?
(The data can be considered static for the duration, no requirement to keep querying the database.)
Would you have two - one for MT and one for ST, each essentially wrapping the underlying table.
Would you have just one that is used to fill both combos (eg. like a tree internally)
Linked with this at the UI level what are your thoughts about refilling the ST combo when the MT combo changes. The form both are on handles that, or the MT combo having a handle to the ST combo and calling that directly to refill ?
I would not pull all the data in at once. I would have the UI capture the change event on the MT combobox, and any time it changes, I would send down for the ST list passing it the selected MT.
The business would just pass a list back up to the UI. No complex structures needed.
The business would just pass a list back up to the UI. No complex structures needed.
ASKER
Thanks
Before jumping into the deep end of the pool, let's look at the existing structure. I've got an issue with the MT/ST relationship. Ideally, the MT object should contain the keys for the ST object(s). The structure that you've described is quite odd.
Kent