RE: [PHP-DB] PHP-mysql better way to build a pyramid table From: Nold, Mark (Mark.Nold <email protected>)
Date: 11/30/00

----------------------------------------------------------------------------
-----------------
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>