Posted by asdf on 02/02/07 03:35

"lorento" <laurente1234@yahoo.com> wrote in message
news:1170384094.157629.187360@q2g2000cwa.googlegroups.com...
>I think it is not a good table design. You need to learn database
> normalization first.
> http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
>
> --
> http://www.mastervb.net
> http://www.theukmap.com
> On Feb 2, 7:58 am, "Phil Latio" <phil.la...@f-in-stupid.co.uk> wrote:
>> Let's say I have a simple web application running with just two MySQL
>> tables. The tables structure is as follows:
>>
>> Table: category
>> category_id (PK)
>> category_name
>> parent_category (FK) //references category_id in this table
>>
>> Table: link
>> link_id (PK)
>> link_name
>> link_description
>> category_id (FK) //references category_id in category table
>>
>> Here is the data in the category table
>> 1, England, 0
>> 2, West Yorkshire, 1
>> 3, Batley, 2
>> 4, Leeds, 2
>> 5, Bradford, 2
>>
>> As you can see Batley, Leeds and Bradford are sub-categories of West
>> Yorkshire which itself is a sub-category of England.
>>
>> What I want to display when I am browsing through sub-categories of links
>> is
>> not only the name of that sub-category but it's category's parents, grand
>> parents and so on like the below example.
>>
>> UK >> West Yorkshire >> Batley
>>
>> I see this on a lot of directory sites but none of my PHP books cover how
>> this is done. Must be quite simple so can someone please point me in the
>> right direction. Hope I've explained it well enough, haven't a clue what
>> this process is called.
>>
>> Cheers
>>
>> Phil
>

It's often called 'breadcrumb navigation'.

Further to lorento's incredibly useful top post (:p)... I think what he's
trying to say that perhaps the relationship between parent and children in
your tree structure (which seems to be what you are trying to represent in
your table), would be best expressed in a separate table... so you'd have
two tables:

Category
-------------------------
category_id (PK)
category_name

Category_Relations
-------------------------
parent_category_id (FK)
child_category_id (FK)

parent_category_id + child_category_id in the second table together would
form a compound primary key for the table.

The result of this, of course, would be that each child *might* become a
child of multiple parents. This could be an advantage or a disadvantage,
depending on how you want the children to behave.

The other approach you could use, I guess, would be to identify the
following entities/tables (... yes, virginia, I know they're not the same
thing):

Country
Region
Town

If your structure requires that you have infinite tree 'nodes' however, this
approach will not work, and so your current structure works better. I guess
it depends on whether you are trying to construct an n-leafed tree, or
enforce strict locality rules... time for a specification?

It is incorrect IMO to say that your current structure is 'not a good table
design', since you may, indeed be trying to construct a 'tree' rather than a
strict entitiy model.

Horses for courses.

[Back to original message]