[RESOLVED] DB Design - alternatives?

simwiz

Hi,

I thought I'd have a look into creating a page structure using Mysql and then fetching the result set to create a nice set of pages/ sub pages.

I wanted to create a quick test to see if the result would work.

PAGE TABLE
pID
pTITLE
pCONTENT
pPubDate

SUBPAGE TABLE
pID
subID

Now lets say the result would be bellow

PAGE TABLE

pID : 1
pTITLE :HOME PAGE
pCONTENT : this is the home page content
pPudDate : 2009-06-17

pID : 2
pTITLE :MEMBER
pCONTENT : this is the members page content
pPudDate : 2009-06-17

pID : 3
pTITLE : USER Mr Bash
pCONTENT : this is Mr Bash's member page
pPudDate : 2009-06-17

SUBPAGE TABLE
pID 2
subID 3

the subpage table basically is saying that ID 2 from table 1 is the parent of ID 3. Now the issue is how can I create an SQL query to list the parent and the child within one table.

PARENT - title CHILD -title
MEMBER USER Mr Bash

this way I can easily output the results without needing two seperate results set within PHP. I'd rather use a complex JOIN etc than creating multiple simple querys and try joining it.

Would it be best to create a query using the above db table design or would you use a different design method?

If anyone could create a query or give me an idea how to create the DB Tables better then please do so.

thank you.

simwiz

Instead of using two tables I could just add a parent ID within the PAGE TABLE

PAGE TABLE
pID
pTITLE
pCONTENT
pPubDate
parentID

Again the only issue is displaying the parent and children (null if pages does not have child) together in one table.

Any help would be appreciated

Bjom

I'm not 100% sure what you want to achieve with this, but if you want to go with a simple DB design using one table, then the approach in your 2nd post will work (I suggest camelcaps instead of all caps and less abbreviations, but that's a matter of taste)

tPage
pageID
pageTitle
pageContent
pagePubDate
parentID

To get things together you dont need a complex join, but a simple one 🙂. You can do a reflexive join, joining the table on itself using aliases for the table names.

for example:

SELECT tPage_A.pageID, tPage_A.pageTitle, tPage_A.parentID, tPage_B.pageTitle AS parentTitle 
    FROM tPage AS tPage_A 
    LEFT JOIN tPage AS tPage_B 
        ON tPage_A.parentID = tPage_B.pageID;

simwiz

Hi Bjom,

You basically explained the type of query I was looking for with an example, brilliant!

I knew what I wanted but if you have never come across a query thats joins to its self so asking for a solution seemed a better option.

Thank You. I will adopt this technique in future.