Need Some Advice on Collections and Search Features..

[JustinStudios]

Not sure if I am doing this correctly and I want to make sure that it is done right. What I am doing is making a website for a client that wants users to be able to add articles to a collection. Their are 4 separate types of collections that can be added and they have a hierarchy like this:

Magazine - Issue -[Pattern] / [Article]

I'm wanting these to appear like this when searched for:

+ Magazine Name
- Issue
-Pattern/Article
-Pattern/Article

I have the database designed with tables for each Category on it's own AND a members table. I use the collections table to link these 3 tables by ID. Also in the collections table I use a char named "Type" to determine whether it is a Magazine, Issue, Pattern, or Article. I then call the Collections table and then I need to pull data from the Members table, then pull data from the Issues table, and it gets into a nasty mess. I know in Access you can create relational databases and was wondering how to do this with php / MySQL to fix my nightmares. I have searched my sources and haven't found anything on it more than opening up new connections constantly and I really don't want to do that. Any tips/suggestions before I go crazy?

[Rakuli]

At first glance I would think you're overcomplicating it a bit.

Can you explain a bit more about what you need and what each of the articles will contain etc.. I think I can offer you a better way to lay out your database.

MySQL doesn't enforce foreign keys as some other databases do (Access for eg.) but a well designed database will have logical relational foreign keys.. by having a column with a character representing the type signals that you aren't using foreign keys effectively.

Cheers

[JustinStudios]

Well this is a collection where users can add things to their collection.

They have the ability to add issues (of a magazine), which is just basic information about an issue with links to all of the articles and patterns that were in that issue, or add individual articles / patterns from that issue to their collection. All of that works ok with the setup I have now, but the problem comes in displaying the collection information.

If they have an issue in their collection I connect to the Issues database and find out what Magazine it is from (by using the Magazine's table foreign key). I then save the information from the issues to variables and then go and connect to the Magazine table and grab the magazine name from there. I then display Magazine name and Issue information from variables. But if the user also has patters / articles, I have to go back and check if those are from the same issue as the previous, if not, I have to check if it is from the same magazine as the previous, if not then I have to go find out what issue it is from so that I can get the magazine name.

This is where I am having my problems. While everything is tied together with keys I am at a loss as to what to do to get this information without using so many connections. I have attached an Excel file that shows the necessary tables from the database. The unused tables were deleted from this file.