If anybody has any websites bookmarked with some good tips on database design, please hand them over, otherwise if you can lend a hand on my following problem I would appreciate it.

I am create a web applicaiton in PHP/MySQL that is going to hold quite a large amount of data. Because of this I would like to have my database set up for the best speed possible. Any tips anbody can toss would be appreciated.

Here's the main problem I'm having with the design. I need to know the most effective way to spider information within a mysql database. Example:

A is linked to B and C, B is linked to 1, C is linked to 2, 1 is linked to Delta (kinda hard to understand I know).

Now when somebody goes's to A's page the software spiders the database and spits out something like:

A -> B -> 1 -> Delta
A -> C -> 2

Any tips?

Hello:

I would suggest looking into MySQL Joins. More information can be read in the links below:

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.shtml

If you still need any additional help or ideas, do feel free to Private Message me and I’ll be glad to help you out with this.

Thank you!

Thanks very much for the site, I have read through most of it and there is a ton of good information in there. Joins were particularly interesting. I might not have stated my mysql use properly though.

What I am worried about is having to query the database too many times to gain information.

Here's a graphic representation of what I'm trying to achieve:

http://occnow.com/image1.gif

When Mike was to log in, a page should be generated that spiders down through all that information and i will create an image similar to that with the data retrieved from the database.

I can create the image, and I could create the database and code to do it, but my way would be something like this:

Have a table with two columns. Every item in the system will have a unique ID. Each relationship would be shown in the table via the two ids in the same row:

mike - project1
mike - project2
mike - project3
project1 - jane
project1 - robert
project1 - tom
jane - a
jane - b

When the information needed to be accessed a function would be called that retrieved everything related to mike and store it in an array.It would then cycle through that array and get everything attatched to the items in that array and put them in their own array. Cycle through, and through, and through.

As you can see on a large system this would result in a massive number of queries which would no doubt slow down the system in a major way. I was just wondering if there was a better way to do this.

Well, if you're using Mysql 4 you an use subselects plus as erod suggested, if you use joins, that will help. A join is still a single query to the DB.

I found the majority of what I was looking for in a nested set design. Thanks everybody ;)