-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
joining in on this problem I have a little question. Is there a
possibility to yquery for all people below e.g. manager 1? Inlcuding
every level of people under that manager? Or do I have to put the
recursive part into my code?
Regards,
Matthias
> Hi Jamil,
>
> There are several ways, but essentially you can do this by
> storing the parent
> child relation ship in a table, and either also storing the emloyee
> information in the relationship table if appropriate, or else in
> tables more appropriate to the type of each entity and determine
> which
> table use from
> a type identifier in the relations table.
>
> So, for example, if you were just storing the names and phone
> numbers of employees, you might consider a schema such as:
>
> (employee_id int unsigned auto_increment not null,
> boss_id int unsigned not null,
> etype enum ('head honcho', 'SVP', 'VP', 'TM', 'TO', 'temp',
> 'service') not null,
> name varchar(50) not null,
> phone varchar(50) not null,
> primary key (boss_id, employee_id),
> index by_etype (etype)
> )
>
> When you insert employees, simply insert the employee with the ID
> of their
> boss. With this schema you can quickly query for the people who
> work for a given employee, and also by type, and you can query for
> all employees too.
>
> However, doing a deep select of all employees below a given
> employee isn't efficient in this scenario. An alternative is to
> allocate a
> range of employee
> ID's available to decendents of a given employee, and to add an
> 'lbound' and 'ubound' field to the schema and a unique index on
> (lbound,ubound). When you add an emplyee you pick
> an unallocated part of the range between lbound and ubound of the
> employee boss and use this to set the new employee lbound and
> ubound
> values. In this
> way you can then query for employess where 'id between l and u'
> where l and u
> are the lbound and ubound of the top level employee that you want
> to query for.
>
> Finally, if you feel that the attributes of employees are too
> different to put all employee types into a single table without
> unacceptable waste of space in unused columns, you could consider
> packing all non-common attributes into a varchar or blob field, and
> retrieving individual attributes either with some string
> manipulation functions or a UDF if you want to hack some code.
> I've tried the approach of putting packed attributes into a field
> with extraction and manipulation via UDF's and this can work
> extremely quickly with even 10's of thousands of rows.
>
> This way you can store an arbitrary set of attributes, different
> per row if you like, in the same table, and do very quick 1 level
> or multilevel searches in a single query. Using the packed
> attributes concept you could add an attributes table that mapped
> attribute type, name, default value, etc. and then store attributes
> in the packed field by ID.
>
> Hopefully there are some ideas that you might find useful!
>
> Good luck,
>
> Nick
>
>
> On Tue, Jul 18, 2000 at 11:06:59AM -0700, Jamil Murji wrote:
> >
> > Anyone know how to implement a hierarchal query?
> >
> > Let's say I have a boss, employee scenario:
> >
> > Boss,
> > manager 1
> > sub manager 1
> > employee 1
> > employee 2
> > employee 3
> > sub manager 2
> > employee 4
> > sub manager 3
> > employee 5
> > temp-employee 1
> > temp-employee 2
> > sub manager 4
> > sub manager 5
> > manager 2
> >
> > etc....
> >
> > How do I set up a table for the above? How can I query this
> table to return
> > the tree? How can I add extra
> managers/sub-manager/employess/sub-employess
> > (and possibly more levels)? Any suggestions....
> >
> > James
> >
> >
> >
> >
> >
> > --
> > ------------------------------------------------------------------
> > --- Please check "http://www.mysql.com/php/manual.php" before
> > posting. To request this thread, e-mail
> mysql-thread44464@stripped
> >
> > To unsubscribe, send a message to:
> > <mysql-unsubscribe-nick=macaw.demon.co.uk@stripped>
> >
> > If you have a broken mail client that cannot send a message to
> the above address(Microsoft Outlook), you can use
http://lists.mysql.com/php/unsubscribe.php
- --
- ---------------------------------------------------------------------
Please check "http://www.mysql.com/php/manual.php" before
posting. To request this thread, e-mail
mysql-thread44482@stripped
To unsubscribe, send a message to:
<mysql-unsubscribe-mkloepper=aolon.de@stripped>
If you have a broken mail client that cannot send a message to the
above address(Microsoft Outlook), you can use
http://lists.mysql.com/php/unsubscribe.php
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.2 for non-commercial use <http://www.pgp.com>
Comment: PGPkey may be obtained from www.aolon.de
iQA/AwUBOXTO/aeBi4YhR3YcEQKTxwCg01b+U/BepVWaolXN13Tfg4J1DzEAoI6C
ybjmqQvH6EVF4hCHwbGqcOdx
=K+RX
-----END PGP SIGNATURE-----
