Hi, I'm developing a web content management system for my website. I'm in a dilemna over which schema would be best for categorizing each document and really would appreciate some experienced input on this since I've changed my mind 4 times already and still can't decide between the two following solutions.
The Problem ~ How to represent a URL which in turn represents the information-tree site-mapping of document. For example /Products/Fruit/Orange /Apple /Meat/Beef /Pork Possible Solution #1 ~ A column for each level. +---+----------+----------+---------+ | id | level1 | level2 | level3 | +---+----------+----------+---------+ | 1 | products | | | | 2 | products | fruit | | | 3 | products | fruit | apple | I won't go on. I can hear people screaming "normalization" already. The only reason I'm even considering this schema is that it appears to be much faster to query the table than possible solutoin #2 described next. Possible Solution #2 ~ Use a parent-child relationship for each node of the category tree with the root node having an id zero. +----------+----+----------+ | parentid | id | node | +----------+----+----------+ | 0 | 1 | products | | 1 | 2 | fruit | | 1 | 3 | meat | | 2 | 4 | orange | | 2 | 5 | apple | | 3 | 6 | beef | create table categories ( parentid int not null default 0, id int not null auto_increment, node char(16) not null default "", primary key (id), index (node) ); Advantage is that it's infinitely scalable. Disadvantage is that to search for the document ID at /Products/Meat/Beef requires a search like this select level3.id from categories as level1, categories as level2, categories as level3 where level1.node = "products" and level1.parentid = 0 and level1.id = level2.parentid and level2.node = "meat" and level2.id = level3.parentid and level3.node = "beef" With so many joins, is this going to be unscalable as we go to multiple depths? Also, is there any way to optimize that query ? Am I correct in assuming that Solution#1 will result in a much faster query (eg. select id from categories where level1 = "products" and level2 = "meat" and level3="beef") Thanks in advance for your help. Stephen. -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php