|
|
Posted by Jan Morten Srensen on 02/08/07 14:04
"P Pulkkinen" <perttu.POISTATAMA.pulkkinen@POISTATAMA.elisanet.fi> skrev i
meddelandet news:39Eyh.7701$T22.2430@reader1.news.saunalahti.fi...
>
> "Jan Morten Srensen" <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.
What then if I wanted to have multiple parents maybe even from different
tree levels?
(Is that possible with your solution?)
I wrote a solution to this last nite after posting this question and it was
smashing. I am thinking about publishing it somewhere because I know others
have the same problem as I do.
--
Jan Morten
http://www.sitconsulting.no
Billig datahjelp i Oslo omrdet.
Navigation:
[Reply to this message]
|