[eluser]Ollie Rattue[/eluser]
Hey, I need a little advice/nudge in the right direction. I have 3 tables:
manufacturers -> id, manufacturer e.g. 20, Apple
category -> id, type -> 35, MP3 Player
models -> id, model, manufacturer, category e.g. 2, Ipod, 20, 35
items -> id, heading, description, manufacturer, model e.g. 10, "Apple Ipod for sale 1 week old", "I am selling my nearly new apple ipod", 20, 2
Right now as you can see I have decided to separate the manufacturers and categories and refer to them by their integar ids. This gives me more flexibility and makes the db more dynamic e.g. if I change a category name it will stay the same id in the models table but the update will "flutter" through the website.
So from thinking it through this seems to be the most logical way to setup my db. Am I right in my thoughts here?
I want to create a webpage where you see all of the manufacturers with their corresponding models when you choose a given category. On top of this I want to do a count of the number of items within each category. So it would look something like this:
Products -> MP3 Players (breadcrumb display)
Apple (10)
-> Ipod classic (1), Ipod Mini (6), Ipod touch (3)
Samsung (15)
-> etc
Thinking this through it is a series of queries with the results from one being used for the other. Maybe I would read my results into one array which I would foreach through to build my browseable webpage.
The process will look something like this:
get id and manufacturerid from models where category = 35 would give me all the models and manufacturer ids that are MP3 players
next query items where manufacturer = the id i just got and model = theidijustgot
this would give me all the items under a certain make and model.
But the thing is I would then need to make this query for every manufacturer and model which I want to check. Is the right way to do it?
This page could end up having 1000's of db queries which I would of thought would slow thepage to a crawl. Luckily this page can be cached say once every couple of hours.
So any input, advice, example code, links would be hugely appreciated. I just don't want to spend massive ammounts of time doing a lot of overly complicated coding.
