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
