|
|
Posted by P Pulkkinen on 02/08/07 11:51
"Jan Morten Sψrensen" <jms@sensewave.com> kirjoitti
viestissδ:52056$45ca8cfe$54d23962$9774@news.chello.no...
> I have this all stored in a mysql-database in the following tables
> Category
> id(integer, autonumber)
> name(text)
> language(integer)
> Category_relationships
> id(integer, autonumber)
> childid(integer)
> parentid(integer)
> language(integer)
¨
Hello. Like other person already mentioned, there is some database
optimisation need. Parent_id in category table itself would be sufficient,
becuse table CAN refer to itself, also. Not that it would greatly make thing
so much different though. But then in quiries you can do SOMETHING like:
select * from category as thechild, category as theparent where
thechild.parentid = theparent.id (and then other conditions);
(sorry if there's some typos etc., my mysql syntax is in "passive memory"
:-)
and you can get information from both the child and its parent. Just as the
other person mentioned, in menu building you start from oldest parents, who
have 0 as parentid. And then recursively downwards, using the id of the
child as new parentid. You follow every branch to the very end (but since
its recursive work, once you get it right in FUNCTION, you dont worry so
much):
Grandpapa#1 -> Papa#1 -> Son#1
Grandpapa#1 -> Papa#1 -> Son#2
Grandpapa#1 -> Papa#2 -> Son#3
Grandpapa#1 -> Papa#2 -> Son#4
Grandpapa#2 -> Papa#3 -> Son#5
Grandpapa#2 -> Papa#3 -> Son#6
Grandpapa#2 -> Papa#4 -> Son#7
Grandpapa#2 -> Papa#4 -> Son#8
Downside is growing number of queries. There are ways, different database
models for this, etc. but it'is also ok to do this, since website menu us
not an excessive list. In the example above, there are 2+4+8=14 categories
and you would get them without trying anything complicated with 7 queries. I
believe that could optimised though.
BTW, after using this model this far, and even after viewing on the surface
alternate models like nested set model, I feel that either xml or an
associative array stored with serialize()-function, would be the most
natural ways to store category strucures, whicha are basicly
multidimensional arrays. But I think that we get used to love database
because of the fun of it (?) and searchability. But if someone just would
make a class that would have some sql-like search features, i'd love to
store caterories in simple xml.
Navigation:
[Reply to this message]
|