Multi-tiered keyword table

[Guest]

Anyone got any ideas about the following.

I have a table of keywords. The keywords can be 3 levels deep so the table
has 3 columns Tier1, Tier2, Tier3. Tier1 is always present but Tier2 and/or
Tier3 may not be. If Tier2 is blank, Tier3 must be blank. I know this is not
normalized.

The user will be able to drill down to the lowest level. ie. I present all
unique Tier1 keywords and a "+" if there are multiple Tier2 for that Tier1.
Then on Tier2 I do the same.

Now the tricky part. I'd like to give the user the option of selecting at
the lowest Tier level directly e.g.
Software-MSOffice--Powerpoint
The user can select 'Powerpoint' directly without having to drill thru
Software and MSOffice. The problem is the lowest Tier can have duplicates e.g.
Hardware-Laptop-Display
Hardware-Computer-Display

I have 95% of this coded and working, the above is the only part I'm unsure
about. I'm thinking I'd show all unique lowest level keywords, then a "+" if
there were duplicates.
The most important requirement of this facility is that it must take the
minimum number of keystrokes to select the keyword.

 

[Guest]

I think it would be easier to restructure your table. The way you structure
the table, you limit yourself to only 3 level down. You have to add extra
field to create additional level.

Create a table with only these fields:
ID = id of keyword
Keyword = name of keyword
SubID = Sub ID of other ID

Example records will be:

ID Keyword Sub ID
1 Fruniture 0
2 Bedroom 1
3 End table 2

Keyword id for bedroom is 2 and it is a subid of 1 (furniture).

Sorry I am suggesting a totally different route, but I think this a better
database design.

 

[Guest]

I know it's a better design but have already decided not to use that method
for other reasons.