NYCPHP Meetup

[Rob Marscher]

I think you'll need a few extra fields in the "Available_Categories" table to get the type of heirarchy you're talking about.  There's a few different types of commonly used tree structures... so you have to figure out which one best fits what you need.  I know we had a discussion about some tree implementation in the last month or two... I forget if it was on this list or the MySQL SIG list.  Also, you can get away without having the ID field in the "Employee" table by using Employee_ID and Category_ID together as the primary key.

-Rob

Peter Sawczynec wrote:
> You should further study the topic: 'database normalization' to get more
> grasp of 
> how to handle table relationships as the industry has generally settled on. 
>
> The following type of three table structure should offer solution to your
> issue. 
> Roughly creating as follows should get you started. 
>
> "Employee" table fields:
> ID 
> Employee_ID
> First_Name 
> Last_Name
> Address
>
> "Employee_Attrributes" table fields:
> ID 
> Employee_ID 
> Category_ID
>
> "Available_Categories" table fields:
> ID
> Category_Name
>
>
> "Employee" table and "Employee_Attributes" tables have an infinitely
> expandable, one to many, primary key to foreign key relationship.
>
> Save a new row entry into "Employee_Attributes" table every time an Employee
> is added to a new category. 
> Then perform multi-table SELECT queries using JOIN, LEFT JOIN, or RIGHT JOIN
> when you need to get the Employee category info.
>
> That should tide you over.
>
> Warmest regards,
>  
> Peter Sawczynec,
> Technology Director
> PSWebcode
> _Design & Interface
> _Ecommerce
> _Database Management
> ps at pswebcode.com
> 646.316.3678
> www.pswebcode.com
>  
>
>
>
> -----Original Message-----
> From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org] On
> Behalf Of LK
> Sent: Friday, September 08, 2006 10:35 AM
> To: talk at lists.nyphp.org
> Subject: Re: [nycphp-talk] PHP and MySQL projects to include in a portfolio.
>
>
> Neil,
>
> I happen to be struggling now with a database issue that might interest you
> and I'd welcome and appreciate suggestions from the group.
>
> My issue is: With a relational database how do you represent and navigate a
> tree with unlimited number of levels and branches ? Example: categorization
> hierarchy. Let's say you have a table of Employees. Now you want to
> categorize them by Job_Type: clerk, secretary, manager, etc. But each one of
> these can be further sub-categorized, e.g. Manager: production, purchasing,
> accounting etc. Each one of those can also be sub-categorized in an
> unlimited recursive fashion. 
>
> One could try constructing a table with columns: level_0 level_1 level_2
> etc. where level_0 holds the 0-th level categories, level_1 - first level
> subcategories, etc. But what if the number of category levels is potentially
> unlimited - what do you do then?
>
> Seems like this must have been dealt with before somewhere, and any
> suggestions and pointers would be greatly appreciated.
>
> Leo Kokin
>
>
>
> --- Neil Argent <neil.argent at gmail.com> wrote:
>
>   
>> Following an absence from work due a long term illness, I have just
>> completed the CIW Master Designer qualification to assist me in my to 
>> return to work.  
>>  
>> To facilitate my return, it is apparent that I need to learn PHP and
>> demonstrate its use with and without MySQL.
>>  
>> Could you suggest examples that I should write and use as part of my
>> portfolio.
>>  
>> I am not looking for detailed descriptions, just brief outlines of
>> projects that will demonstrate the skills being considered for PHP
>> employment at this time.  
>>  
>> I have some experience of using PHP5 and PHP4 with MySQL, and a lot 
>> more
>> experience in C++, so I am not coming at it as a complete programming 
>> novice.
>>
>> Thanks.
>> _______________________________________________
>> New York PHP Community Talk Mailing List 
>> http://lists.nyphp.org/mailman/listinfo/talk
>>
>> NYPHPCon 2006 Presentations Online
>> http://www.nyphpcon.com
>>
>> Show Your Participation in New York PHP 
>> http://www.nyphp.org/show_participation.php
>>
>>     
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
> _______________________________________________
> New York PHP Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> NYPHPCon 2006 Presentations Online
> http://www.nyphpcon.com
>
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php
>
> _______________________________________________
> New York PHP Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> NYPHPCon 2006 Presentations Online
> http://www.nyphpcon.com
>
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php
>
>