Hello fellow sitepointers :-)
I was wondering if one of you might be willing to give me a hand with a certain database design query that has me a little stumped.
Having diligently red Kevin Yank's brilliant "Build Your Owen Database Driven Web Site Using PHP & MYSQL 4", and produced a couple of websites with increasing levels of database interactivity for clients - admittedly, with some help from the sitepoint forums - the next challenge that I've chosen to accept is to code a (relatively simple) CMS.
I've got my head round a good number of the basics, but am having problems trying to decide how to structure the database design, specifically as regards ordering the page structure, to allow pages to be "nested" in a parent/child relationship. I understand that this will likely involve two or three separate tables, which will be brought together in an INNERJOIN (?), to establish where in the structure each page sits, but am unsure and a bit nervous as to how to approach this.
Currently, I've got a set number of top-level pages or categories - 6, to be exact, under each of which I'd like to have a number of second-level pages. A few of these second level pages will then have child pages of their own, and the site admin needs to be able to create, edit and delete these pages' content and position within the structure at any time, as necessary.
On the front-end, all of the content will be accessible through a main drop-down nav menu - fairly simple stuff.
So, in a nutshell, I'd love some advice with:
> how to structure the database (eg: different tables for pages, pagecategories, categories and using INNERJOINS where necessary)
> how I might present a formatted list of these pages in a tabulated way in the admin area of the CMS, to allow the admin to go in and edit any page directly without having to navigate down the structure first.
> I'll worry about how to import a list of links into the frontend to populate the drop-down at a later point, but any passing advice would be helpful
Cheers guys & gals for any help and advice - it is really much appreciated