php-db | 2000121

Re: [PHP-DB] PHP-mysql better way to build a pyramid table From: Uioreanu Calin (uioreanu <email protected>)
Date: 12/05/00

Hello,

 sorry for delay.
 No, i was't asking just for my knowledge. I already designed the scripts
They are online at http://www.ecomert.f2s.com/mlm and running.
 The total_refferal field is calculated at each new subscription. I did't
prevent
recursive calls. I suppose I'll memorize my path in an array and test each
step if the current id hasn't been parsed before.
 I made the calculus at subscription because i wanted to avoid recursive
calls
on relation-ship database at runtime (Member Area). I didn't want to
overload
the db server.

 In fact, the question isn't quite PHP. It's database design, and
optimisation.
 Thanks for all your advice. If you want to discuss the code, I'll show it
to you. 

--
Cordialement
------------------------------------------------------
Calin Uioreanu
ROMANIAN MALE NAME!!
:)
udmsromkt <email protected>
DMS Team Cluj-Napoca Romania
-----------------------------------------------------
"Doug Semig" <dougslist <email protected>> wrote in message
news:3.0.6.32.20001130233734.007eeb70 <email protected>
The original question Calin was asking was how to implement a tree
structure where each child has one parent and each parent can have many
children.  This is most commonly done in a single table which is joined to
itself for processing rather than joining three tables (the original table,
a relationship table, and the original table again).

Your diamond shaped relationships would, of course, require a relationship
table as the original poster had initially designed.  But I have not run
across a diamond shaped relationship yet in reality and haven't given it
much thought.  I suppose it could be used to show some family trees such as
the ancient royals?

In my opinion, this is one of the few times that storing a calculated field
such as total number of decendents (Calin's "total_referrals" attribute)
would be beneficial because of the extraordinary (meaning not ordinary, not
fantastically huge--even though it probably is fantastically huge) cost of
calculating the value.  Even though it does involve more work during
insert, update, and delete operations.

In the original question, Calin had designed in fields for both total
number of decendents AND the number of children that have the particular
node as a parent (Calin's "personal_referrals" attribute).  The number of
children that have any particular node as a parent is, of course, extremely
easy to get, and should not be stored.

Your idea of denormalizing the data for warehousing is definately something
Calin should consider.

But you read a lot more into Calin's original post than I did.  I thought
he (sorry, I'm not familiar with whether Calin is a masculine or a feminine
name) was pretty far along already and already grasped the concept.  So I
thought he was only asking basically how this kind of thing is usually
done.  It's not often I see such a complex relationship already modelled so
well on this list.

Doug

At 12:29 PM 12/1/00 +1030, Nold, Mark wrote:
>---------------------------------------------------------------------------
-
>-----------------
>Disclaimer: The information contained in this email is intended only for
the
>use of the person(s) to whom it is addressed and may be confidential or
>contain legally privileged information. If you are not the intended
>recipient you are hereby notified that any perusal, use, distribution,
>copying or disclosure is strictly prohibited. If you have received this
>email in error please immediately advise us by return email at
>postmaster <email protected> and delete the email document without making a
>copy.
>---------------------------------------------------------------------------
-
>-----------------
>Doug
>
>If a person can only have one parent you can change to using one table by
>simply adding a iduser_parent to the tbl_mlm_users.
>
>Or you may wish to separate the data (as you've done) for clarity or even
>for history. Consider what will will happen if somehow a parent id changes
>(maybe the parent dies, leaves the system or whatever). Using two tables
>will allow you to deal with this easier (plus allow for more complex
>relationship later on)
>
>BUT...
>
>By have the relationship in a separate table you open yourself up to
>creating diamond relationships where a parent is the child of its
grandchild
>(hopefully this makes sense). You will always have to keep this in mind, as
>any recursive functions may go one for ever. (This can be controlled by
>having the child_id only as the Primary key, but then you may as well use
>one table)
>
>AND...
>
>Things like total_referals are redundant as you should calculate them at
>anytime. You dont want to have to update this table constantly with this
>info. Keeping it in sync will be a pain.
>
>BUT...
>
>Treewalking to calculate all this information can be slow and painful. I
>would recommend getting your base tables correct and normalised, then build
>some queries to do you calcs etc. Once you have your queires set use these
>queries to build up datawarehouse style tables of denormalised data. The
>important point here is separate the two lots of tables as your design of
>the base tables will be constant, but you information requirements can (and
>will) change dramtically over time.
>
>Hope this helps,
>
>mn
>
>
>
>Mark Nold
>markn <email protected> <mailto:markn <email protected>>
>Senior Consultant
>
>Change is inevitable, except from vending machines.
>
>
>
>-----Original Message-----
>From: Doug Semig [mailto:dougslist <email protected>]
>Sent: Thursday, November 30, 2000 11:49 PM
>To: php-db <email protected>
>Subject: Re: [PHP-DB] PHP-mysql better way to build a pyramid table
>
>
>This is more commonly done in a single table instead of two tables.
>
>Doug
>
>At 03:45 PM 11/30/00 +0200, Uioreanu Calin wrote:
>>Hello,
>>
>> I want to create through a few number of tables with fewest records
>>a pyramid structure like this: a new person cames into the person's
>>table reffered by another one. There are fields for direct reffered
persons
>>and total refferals from each existing person in person's table. So each
>>new person increases his (parent's) direct reffered persons and each
>>of his parent's parent's and grandparents and ... total refferals.
>>
>> I builded these tables:
>>CREATE TABLE tbl_mlm_users (
>>   username varchar(50) NOT NULL,
>>   userpass varchar(50) NOT NULL,
>>   personal_refferals int(11) DEFAULT '0' NOT NULL,
>>   total_refferals int(11) DEFAULT '0' NOT NULL,
>>   iduser int(11) NOT NULL auto_increment,
>>   userfullname varchar(50) NOT NULL,
>>   useradress varchar(50) NOT NULL,
>>   useremail varchar(50) NOT NULL,
>>   PRIMARY KEY (iduser)
>>);
>>&
>>CREATE TABLE tbl_mlm_refferals (
>>   iduser_parent int(11) DEFAULT '0' NOT NULL,
>>   iduser_child int(11) DEFAULT '0' NOT NULL,
>>   ref_time varchar(20) NOT NULL,
>>   PRIMARY KEY (iduser_parent, iduser_child)
>>);
>>
>>
>> where id.. points to the other table for details.
>>ref_time is the time when the reffer occurs.
>>
>> Is there a better way of doing that?
>>
>>--
>>Regards,
>>------------------------------------------------------
>>Calin Uioreanu
>>udmsromkt <email protected>
>>DMS Team Cluj-Napoca Romania
>>-----------------------------------------------------
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-db-unsubscribe <email protected>
For additional commands, e-mail: php-db-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-db-unsubscribe <email protected>
For additional commands, e-mail: php-db-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>