Categories & sub-categories & sub-sub-categories ...

[plenderj]

( the below is just my idea of how to do it - if you have a better way please let me know because I think this is crap )

I am developing an on-line shop, but need some way to lay out the categories of products.
I have a data file, and a section of the file is in this format :

SAP_Code Main Category|Secondary Category|Third Category Product Name Some other crap blah1 blah2 blah3

So, overall its a tab delimeted file, but the second field, is a "|" pipe delimeted field that specifies the category of the product.
Here is a sample. I've included chunks and chunks of data from the file in the next post.
Its the "Section Text" field that I'm interested in.
It is in the form, as above : Main Category|Secondary Category|Third Category

What I was thinking, as a way to store the data in a DB, and be able to show the categories is :
Create a collection
Run through list line by line
For each line, extract the "Section Text" field
Give each part of the section text field an index - i.e. a number

So for example, if the below file is intact, and there weren't missing parts :

Backup Solutions|Backup Software|Computer Associates
0|1|2

Backup Solutions|Backup Software|Veritas
0|1|3

Backup Solutions|Streamers|Apple
0|4|5

Backup Solutions|Streamers|Hewlett Packard
0|4|6

Boards (video, sound, .)|Card Accessories|Adaptec
7|8|9

Boards (video, sound, .)|Graphics Cards (incl. CAD)|Apple
7|10|11

Boards (video, sound, .)|Graphics Cards (incl. CAD)|Compaq
7|10|12

Boards (video, sound, .)|Graphics Cards (incl. CAD)|Creative Labs
7|10|13

Boards (video, sound, .)|Graphics Cards (incl. CAD)|Gainward
7|10|14

Boards (video, sound, .)|Graphics Cards (incl. CAD)|Hewlett Packard
7|10|15

Boards (video, sound, .)|Host Adapters|Adaptec
7|16|17

Boards (video, sound, .)|Host Adapters|Belkin
7|16|18

Now, for the sake of the shortness of this post, I'll call the fields a|b|c
'a' is the main header, which appears on the front of the website.
Click on 'a', and you'll see every 'b' that's a sub category of a
Click on 'b', and you'll see every 'c' that's a sub category of b
Click on 'c', and you'll see every product that's of type a, b, c

I will store each 'a', its index, and the textual description of it
I will also store with that, each 'b' index that's a part of it.
e.g. :
0,Backup Solutions,1|4
7,Boards (video, sound, .),8|15

I will store each 'b', its index, and the textual description of it
I will also store with that, each 'c' index that's a part of it
e.g. :
1,Backup Software,2|3
4,Streamers,5|6
8,Card Accessories,9
10,Graphics Cards (incl. CAD),11|12|13|14|15
16,Host Adapters,17|18

I will store each 'c', its index, and the textual description of it
I will also store with that, each SAP Code/Product Reference index
e.g. :
2,Computer Associates,791755|791772|872095
3,Veritas,804202|749605|755887
5,Apple,855077|812664|812665
6,Hewlett Packard,758769|759547|743217

So. When the user comes to the main page, the database can pull the indices and descriptions from the main headers table :
Backup Solutions
Board (Video, sound, .)
etc.

When a user clicks on one, I can pass the index of that header back into the DB to pull the second level headers :
Backup Solutions
Backup Software
Streamers

When a user clicks on a sub header, I can again pass the index back into the DB, and pull the further sub header :
Backup Solutions
Backup Software
Computer Associates
Veritas

When a user clicks on a sub header, I can pass that back into the DB, and pull all products listed there :
Backup Solutions
Backup Software
Computer Associates
BrightStore Arcserve Backup Version 9 Tape Raid Option ...
BrightStore Arcserve Backup Version 9 Tape Library Option ...
BrightStore Arcserve Backup Version 9 Tape Universal Client Agent ...



Soooooooo. Any thoughts ?

[plenderj]

Product reference	Section Text	Short description	Detail URL or file	Trade Price	Price	Detail link text	Full Description	RRP	Stock
791755	Backup Solutions|Backup Software|Computer Associates	BrightStor Arcserve Backup Version 9 Tape Raid Option for Netware		506		Product Information	BABFBNW900NE2	0	0
791772	Backup Solutions|Backup Software|Computer Associates	BrightStor Arcserve Backup Version 9 Tape Library Option for Netware		455		Product Information	BABFBNW900NE4	0	0
872095	Backup Solutions|Backup Software|Computer Associates	BrightStor Arcserve Backup Version 9 Universal Client Agent for windows Upgrade from any previous evrsion 		69		Product Information	BABWUP2900E22?BDL	122	0
. . . 
804202	Backup Solutions|Backup Software|Veritas	Backup Exec,Windows,Microsoft Exchange Server Agent Upgrade,v9.0,E/F/G/S/I/C,Full Package Product 		114		Product Information	E094018	0	14
749605	Backup Solutions|Backup Software|Veritas	NetBackup BusinesServer NDMP Agent version 4.5, Cross Platform		1043		Product Information	A08587C-000000	0	0
755887	Backup Solutions|Backup Software|Veritas	Netbackup BusinesServer Exchange Agent version 4.5 VS1 12x5 1yr		144		Product Information	W085805-000112	0	0
. . .
855077	Backup Solutions|Streamers|Apple	60Gb Ultra ATA/100 Drive Module For Apple Xserve	http://www.apple.com/uk/xserve	217		Product Information	M9262G/A	230	0
812664	Backup Solutions|Streamers|Apple	Xserve RAID External Storage 720Gb 4 Drives 2x2 Fibre Channels	http://www/apple.com/uk/xserve	5297		Product Information	M8668B/A	6083	0
812665	Backup Solutions|Streamers|Apple	Xserve XRAID 1260Gb 7 Drives 2x2 Fibre Channels	http://www.apple.com/uk/xserve	6568		Product Information	M8669B/A	7543	0
758769	Backup Solutions|Streamers|Hewlett Packard	DLT VS 40/80 External (Intl)	http://www.hp.com/uk	1276		Product Information	280129-B31	1466	1
759547	Backup Solutions|Streamers|Hewlett Packard	HP Surestore Ultrium 230e exrternal LTO SCSI drive	http://www.hp.com/products1/storage/...	2925		Product Information	C7401B#ABU	3273	1
743217	Backup Solutions|Streamers|Hewlett Packard	HP Surestore DAT40e external SCSI DDS4 drive	http://www.hp.com/products1/storage/...l	748		Product Information	C5687B#ABU	871	0
. . .
333662	Boards (video, sound, .)|Card Accessories|Adaptec	ACK-SS26-50LD Cable 26pin Slim SCSI External to 50pin LD External Fast+Ultra SCSI 36"	http://www.adaptec.com	32		Product Information	ACK-SS26-50LD SLIMSCSI	0	0
450623	Boards (video, sound, .)|Card Accessories|Adaptec	ACK-68I3-LVD Cable 68pin High Density Internal 3 position Ultra 160 SCSI .75m - 10 Pack	http://www.adaptec.com	568		Product Information	ACK-68I3-LVD 10PK	0	0
36894	Boards (video, sound, .)|Card Accessories|Adaptec	ACK-W2W-5I Cable 68pin HD Internal 5 Position Wide SCSI 1.1m	http://www.adaptec.com	47		Product Information	ACK-W2W-51	0	0
. . .
750244	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Apple	NVIDIA GeForce 4 Titanium AGP 128Mb for Mac		377		Product Information	M8757G/A	470	0
572247	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Compaq	NVIDIA Gloria III Quadra 2 PRO AGP	http://www.compaq.co.uk/products/pro...m	603		Product Information	225339-B21	643	0
860758	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Compaq	HP nVidia Quadro4 100NVS 64MB PCI DH with VGA, DVI and S-video	http://h30143.www3.hp.com/configure2...23	90		Product Information	DC664A	0	0
798123	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Compaq	HP nVidia Quadro4 980XGL 3D/AGP 128MB	http://www.hp.com/uk	530		Product Information	AA623A	606	0
827365	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Creative Labs	3D Blaster GeForce FX5600 Ultra - 128Mb AGP with TV Out	http://www.creative-europe.com	179		Product Information	70GB000002013	243	47
809971	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Creative Labs	3D Blaster 4 Titanium 4800 SE - 8x AGP 128Mb DDR VGA & DVI + TV out	http://www.creative-europe.com	178		Product Information	70GB000002005	243	0
873026	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Creative Labs	3D Blaster 5 FX5200 8X PCI, 128 Mb DDr Memory, TV-Out, 128 bit, full support for Microsoft Direct X 9.0	http://uk.europe.creative.com/produc...=4	74		Product Information	70GB000002018	114	74
. . .
886409	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Gainward	Gainward CoolPC/Water cooling upgrade for CPU and NB		117		Product Information	471846200-5292	0	0
886434	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Gainward	Gainward FX Ultra880TV-DVI/AGP 8x 256MB 5ns DDR FX5700 chipset with TV-Out and CRT or DVI		155		Product Information	471846200-5490	0	0
886435	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Gainward	Gainward FX Ultra860TV-DVI/AGP 8x 128MB 5ns DDR FX5700 chipset with TV-Out and CRT or DVI output		134		Product Information	471846200-5513	0	0
. . .
728705	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Hewlett Packard	HP ATI Radeon 7000 Pro 32MB AGP Video Card	http://www.hp.com/uk	115		Product Information	A8049A	124	0
842641	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Hewlett Packard	HP nVidia Quadro4 100NVS DVI connector cables (for use with DC665A)	http://h30143.www3.hp.com/configure2..._1	27		Product Information	DC157A	29	0
858843	Boards (video, sound, .)|Graphics Cards  (incl. CAD)|Hewlett Packard	ATI FireGL T2-64s Graphics Card	http://www.hp.com	222		Product Information	DK599A	252	0
. . .
764440	Boards (video, sound, .)|Host Adapters|Adaptec	ASR-2010S Zero Channel SCSI RAID 2010s Card Ultra 320 - Kit	http://www.adaptec.com/worldwide/pro...u	227		Product Information	ASR-2010S KIT	0	9
855259	Boards (video, sound, .)|Host Adapters|Adaptec	AAR-1210SA Kit 2 Port Serial ATA RAID CardSupports RAID Levels 0,1 and JBOD	http://www.adaptec.com/worldwide/pro...D	516		Product Information	2020500	0	0
713239	Boards (video, sound, .)|Host Adapters|Adaptec	AUH7000PLUS/XHub 7 Plus USB 2.0 7p Hub	http://www.adaptec.com	65		Product Information	AUH-7000PLUS/UK KIT	0	0
584715	Boards (video, sound, .)|Host Adapters|Belkin	Firewire PCMCIA 	http://catalog.belkin.com/IWCatProdu...2F	71		Product Information	F5U512U	97	40
567568	Boards (video, sound, .)|Host Adapters|Belkin	USB 4-Port PCI Card with Quadrabus Technology	http://catalog.belkin.com/IWCatProdu...57	29		Product Information	F5U006-UNV	36	11
633099	Boards (video, sound, .)|Host Adapters|Belkin	PCI card with 5 ports USB 2.0 	http://www.belkin.com	35		Product Information	F5U220	43	65
598626	Boards (video, sound, .)|Host Adapters|Belkin	Card PCMCIA IEEE1394 FWire for Mac	http://www.belkin.com	72		Product Information	F5U512UMAC	97	4
796967	Boards (video, sound, .)|Host Adapters|Compaq	HP SCSI/PCI U320SCSI f WS XW	http://www.hp.com/uk	189		Product Information	AA611A	202	0
807882	Boards (video, sound, .)|Host Adapters|Compaq	BL20 G2 pClass Fibre Channel Mezzanine card	http://www.hp.co.uk	790		Product Information	300874-B21	908	0
566118	Boards (video, sound, .)|Host Adapters|Compaq	Smart Array 2100S PCI for Proliant	http://www.compaq.co.uk/products/pro...m	437		Product Information	192842-B21	467	0
807853	Boards (video, sound, .)|Host Adapters|Compaq	Smart Array 641 PCI-X controller 	http://h18004.www1.hp.com/products/s...l	397		Product Information	291966-B21	456	55
483757	Boards (video, sound, .)|Host Adapters|Hewlett Packard	Adapter 64Bit PCI SCSI	http://www.compaq.co.uk/products/pro...	2765		Product Information	168794-B21	0	0
478518	Boards (video, sound, .)|Host Adapters|Hewlett Packard	Adapter PCI for  RA8000 ESA 12000	http://www.compaq.co.uk/products/pro...	1822		Product Information	176479-B21	2093	0
570832	Boards (video, sound, .)|Host Adapters|Hewlett Packard	HP Adapter PCI SCSI for DLT	http://www.hp.com	263		Product Information	C7474A	295	1
704503	Boards (video, sound, .)|Host Adapters|Hewlett Packard	2Gb Fibre Channel HBA ForWNT/W2K (FA2101)for MSA 1000		1368		Product Information	245299-B21	1571	0
850607	Boards (video, sound, .)|Host Adapters|IBM	RAID Controller/6i U160+320SCSI		369		Product Information	71P8595	448	0
850870	Boards (video, sound, .)|Host Adapters|IBM	RAID Controller/6M U320SCSI 256MB		1025		Product Information	02R0988	1248	0
482068	Boards (video, sound, .)|Host Adapters|IBM	Adapter 2port for FAST T500 PCI connection GENet card	http://www.pc.ibm.com/uk/options.html	745		Product Information	00N6882	0	0
. . . 
480787	Boards (video, sound, .)|Multifunction Cards|Hauppauge	WinTV USB FM /External USB TV tuner includes Fast Teletext, FM Radio and S-Video connection, External for notebooks and PC supports Win 9x, 2000, XP. Ships with all Cables	http://www.hauppauge.com/hcw/index.htm	57		Product Information	573	0	48
632344	Boards (video, sound, .)|Multifunction Cards|Hauppauge	WinTV Nova-T PCI /Freeview Digital Terrestrial MPEG2 PCI card with TV adapter + Digital Teletext + Video recorder. Supports Win 9x,2000,XP		98		Product Information	909	0	178

[CyberSurfer]

if you want to make it reeeeeealy easy, get your hands on WebZinc, Karl Moore's hardcore web manipulation product. The text parsing parts would be good for what you want, and if you're working on an online shop, the rest of the functionality may be too

[Danial]

will store each 'a', its index, and the textual description of it
I will also store with that, each 'b' index that's a part of it.
e.g. :
0,Backup Solutions,1|4
7,Boards (video, sound, .),8|15

Hi Jamie,

Seems like a decent architecture, i havent given it much thought but you may have made a simple mistake which is, instead of storing the index of b in a, you should store index of a in the table b as foreign key.

Same with the table b and c, i assume its a one 2 many relation ship so the many end should contain the index.

If i come up with anything else, i will post it here.

[alex_read]

If you're going down the db route - looks fine! I'd go along with that last post by Danial too

[ironhide]

Why would you do it that way?

For each item, just include a ParentID. For each new category assign a unique GroupID.