-
Feb 24th, 2017, 02:24 PM
#1
Thread Starter
Lively Member
Create Tiers Logic for unlimted
Hi,
Not sure where to ask this but will try here first.
I am trying to put together a piece of software which will walk you thru a task by giving appropriate answers depending on your selection from drowdown list
but struggling with the logic of how to reference each option, and best way to build tables
I was thinking tables like Tier1, tier2, tier3 etc
where tier1 has ID, Option
Tier2 has ID, Tier1ID, Option (Tier1ID = tier1 ID)
Tier3 has ID, Tier2ID, Option (Tier2ID = tier2 ID)
etc.
this is ok for upto 3-4 tiers but then just to many references
i.e. data example
Option 1 Option 1 Option 1
Option 1 Option 1 Option 2 Option 1
Option 1 Option 1 Option 3 Option 1 Option 1
Option 1 Option 2 Option 1 Option 1 Option 2 Option 1
Option 1 Option 3 Option 1 Option 1 Option 1
Option 1 Option 3 Option 1 Option 2
Option 1 Option 3 Option 2 Option 1
Option 1 Option 3 Option 2 Option 2
Option 1 Option 3 Option 3 Option 1 Option 1
Option 2 Option 1 Option 1
Option 2 Option 2 Option 1
Option 2 Option 2 Option 2
Option 2 Option 3 Option 1
Option 2 Option 4 Option 1
Option 3 Option 1
Thanks
Last edited by jpskiller; Feb 24th, 2017 at 02:49 PM.
-
Feb 24th, 2017, 02:56 PM
#2
Re: Create Tiers Logic for unlimted
1 table...
No need for multiple tables....
ID, Name, ParentID, etc...
1, Option 1, Null
2, Option 2, 1
3, Option 3, 1
from that it's possible to walk the options.... select Options where ParentID is null... that's the start... then when one selected, you select Options where the ParentId is the selected ID...
In the sample data I've given, if the user selects Option 1, they are then presented with Options 2 & 3 ...If they select Option 2, then I'd select Options where ParentID = 2 and so on.
-tg
-
Feb 24th, 2017, 04:20 PM
#3
Thread Starter
Lively Member
Re: Create Tiers Logic for unlimted
thanks I'm still stuck with multi tables in my head and cant see how to structure a flat table with all options that relate back to each other on deeper levels, I know that is the best way to go but still cant fully visualize it.
I sound a bit dumb now but I still cant see how I can link it to show all options when selected Option 1 -> Fred - Sam
Where Option 1 has Fred, John
and Fred has Sam, Ian
and John has Paul, Mick
Thanks for help
Last edited by jpskiller; Feb 24th, 2017 at 04:39 PM.
-
Feb 24th, 2017, 04:54 PM
#4
Re: Create Tiers Logic for unlimted
OK, so you start with Option 1... you show Sam and Fred...
Then, they select Fred...
so you select where = to Fred... which is Sam & Iam ... so now you display that...
IF you're looking for ONE query that shows EVERYTHING all at once... then that's a bit more complicated - but doable, I have to do that all the time... but it involves some hoops, and depends on the database system you're using...
-tg
-
Feb 24th, 2017, 06:13 PM
#5
Thread Starter
Lively Member
Re: Create Tiers Logic for unlimted
Hi
Database is SQL server 2005 and front end will be done in vb 2010
Options are not unique, option 4 options could be same as another options 3 etc.
if I can see an example table which has at least 3 levels and with the SELECTS that would probably help, if you know any good links that breaks it down that much I would appreciate it.
-
Feb 25th, 2017, 02:57 PM
#6
Thread Starter
Lively Member
Re: Create Tiers Logic for unlimted
Thanks techgnome for your help I have managed to find information about The Adjacency List Model from http://mikehillyer.com/articles/mana...data-in-mysql/
With help of you and this I have done the following which gives me the basic functions.
I created a Table which contains = ID Autoincremental), Name, ParentID
SQL Code to add example test data
Code:
INSERT INTO t_Tiers VALUES('ELECTRONICS',NULL),
('TELEVISIONS',1), ('PORTABLE ELECTRONICS',1),
('TUBE',2), ('LCD',2), ('PLASMA',2), ('MP3 PLAYERS',6), ('FLASH',7),
('CD PLAYERS',6), ('2 WAY RADIOS',6)
INSERT INTO t_Tiers VALUES('ROOT 2',NULL),
('ROOT 2_1',11),('ROOT 2_2',11),('ROOT 2_3',11),
('ROOT 2_2_1',13),('ROOT 2_2_2',13)
Next I have created a VB 2010 project.
Add 4 comboxs to a form and then add a dataset to project which is contains the new table.
code to fill tables from selections.
Code:
Public Class Form1
Dim dv As DataView
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Me.T_TiersTableAdapter.Fill(Me.DataSet1.t_Tiers)
dv = New DataView(Me.DataSet1.t_Tiers)
dv.RowFilter = "ParentID Is Null"
ComboBox1.DataSource = dv
ComboBox1.ValueMember = "ID"
ComboBox1.DisplayMember = "Name"
End Sub
Private Sub ComboBox1_SelectionChangeCommitted(sender As System.Object, e As System.EventArgs) Handles ComboBox1.SelectionChangeCommitted
dv = New DataView(Me.DataSet1.t_Tiers)
dv.RowFilter = "ParentID = " & ComboBox1.SelectedValue.ToString
ComboBox2.DataSource = dv
ComboBox2.ValueMember = "ID"
ComboBox2.DisplayMember = "Name"
End Sub
Private Sub ComboBox2_SelectionChangeCommitted(sender As System.Object, e As System.EventArgs) Handles ComboBox2.SelectionChangeCommitted
dv = New DataView(Me.DataSet1.t_Tiers)
dv.RowFilter = "ParentID = " & ComboBox2.SelectedValue.ToString
ComboBox3.DataSource = dv
ComboBox3.ValueMember = "ID"
ComboBox3.DisplayMember = "Name"
End Sub
Private Sub ComboBox3_SelectionChangeCommitted(sender As System.Object, e As System.EventArgs) Handles ComboBox3.SelectionChangeCommitted
dv = New DataView(Me.DataSet1.t_Tiers)
dv.RowFilter = "ParentID = " & ComboBox3.SelectedValue.ToString
ComboBox4.DataSource = dv
ComboBox4.ValueMember = "ID"
ComboBox4.DisplayMember = "Name"
End Sub
End Class
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|