Database design query - simple cms

[atlr]

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

Al

[guido2004]

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

If you're talking about a parent-child relationship (pages-subpages-subsubpages), then take a look at Categories and Subcategories.

[atlr]

If you're talking about a parent-child relationship (pages-subpages-subsubpages), then take a look at Categories and Subcategories.

Hi guido2004.
Many thanks for this, I'm taking a look at your helpful link and will post here once I've a) understood it or b) have more questions!

Meanwhile, if anyone has any other comments/suggestions, that'd be great.

Cheers

Al

[IBazz]

I would suggest a workflow like this: (not exhaustive but it's the key things that jump out at me from when I was a beginning beginner).

1. write out (pen and paper), the headings for the data you need to store eg home page, location page, admin name, address_line1, address_line2, state, county, country, etc.
2. group them into categories eg 'general pages', contact data, address data etc.
3. use the categorisation to build the tables with an engine of INNODB and FOREIGN KEY CONSTRAINTS where appropriate.
4. check normalisation and constraints are correctly set up.
5. then, and I think, only then, plan your queries. Don;t store data in a way that you think will make for an easier query. Later on, you'll need to query for something else and such manipulation for the first query 'will' make the other even more difficult.

hth

bazz

[IBazz]

can you explain the parent/child relationship for your web pages?

[atlr]

Hey everyone,

Thanks for the replies.
@guido2004: Okay, I've read through the sqllessons.com link that you posted. This all makes good sense. What I'm now concerned with doing is how I export that data from the DB and make use of it - ie. display it properly - in both an admin area (for a quick overview of page structure, with links next to each page to allow editing) and also in a drop-down menu frontend (presumably, via the UL lists that the author suggests).

I see that a number of JOINS are necessary. No problems there. However, my system could potentially have pages going a good number of levels deep (not just the four that is suggested in the article). How would I get my JOINS to repeat until all of the sub-levels are collected out of the DB? Moreover, how would I then display these properly in a table, with each "sub" level displayed indented to its parent, to clearly visually denote a parent/child relationship between the pages.

@IBazz - I'm not really sure I understand either of your posts properly! Really sorry, it does look helpful, but I don't understand your terminology.

Thanks guys :-D

Al

[IBazz]

@IBazz - I'm not really sure I understand either of your posts properly! Really sorry, it does look helpful, but I don't understand your terminology.

Sorry.

I would suggest you search in your favourite search engine for the words in capitals.

INNODB
FOREIGN KEY CONSTRAINTS
NORMALISATION
PRIMARY KEY

Basically, innodb is a storage engine used for tables and so, if you have a primary key (a record identifier), in one table and it is related to a corresponding key in another table (a foreign key), you will be using those to join the tables on when performing a query. By using INNODB, (as opposed to MyIsam for example), MySQL allows you to enforce REFEERENTIAL INTEGRITY by applying CONSTRAINTS. This means that (if used correctly), that if deleting a parent key, the db will folllow your constraint (rule), to determine what to do with the corresponding key in the other (child) tables. (four or five alternatives available). It keeps the data tidy and can prevent mistakes in data management that may only become evident later, when it may be too late.

You've a bit of a learning curve ahead of you but keep at it and keep asking questions here as you (will), progress.

hth

bazz

[atlr]

@IBazz - thanks for your reply.
I understand what you're getting at, although I'm going to be using an adjacent model. I'm pretty sure that's the best way to go, as I'm not going to be having *loads and loads* of pages on this CMS. I've look at the SitePoint tutorial here: Storing Hierarchical Data in a Database Article » SitePoint which seems helpful, but am a little worried as it was written in 2004. Do you know if this is still relevant or do SP keep all of their old tutorials online, even if they're no longer relevant/compliant/etc?

Cheers for your continuing help

Al

[r937]

the sitepoint article is still relevant, and so is the sqllessons one

to answer your other question, all you need for the admin interface is the sitemap page, which would be a three-level join as shown in the sqllessons article, along with an edit button or link beside each

actually, you might want two tables, now that i think about it

the first is for your pages, including the site structure hierarchy which is inherent in the parentid column

the second is for your page content, and it's in a one-to-zero-or-one relationship -- each page may have content, a TEXT column, which is the only column besides the page id foreign key

the main pages table is thus shorter, so more efficient for self-joins, and it's innodb

meanwhile, the content table is myisam, which allows fulltext searching

i hope this didn't mess you up too much

[DatabaseDesigner]

May I suggest the use of a Unary relationship to handle your parent/child relationships?
Put simply, your table would have its usual id (primary key) and a foreign key maybe named PID or parent_id (whatever your preference. The foreign key will represent a record within the same table. Example:
ID = 1, PID = NULL, name = home
ID = 2, PID= 1, name = company
ID = 3, PID = 2, name = mission statement
ID = 4, PID = 1, name = login

Looking at this example, we can see that the first record is the home page, the mother page. This will not have a PID as it is the highest level and has no parent.
The second record has a PID of 1 to show that this record is a child of the home page.
The third record has a PID of 2 making the second record the parent of this one.

This technique is ideal for what you are after as it means you don't need multiple tables to accommodate the different levels in your hierarchy, and the hierarchy is unlimited. Although it is highly unlikely that you would go any deeper than maybe three levels, you have the luxury of having unlimited hierarchical scope.

Does this make sense?

Please feel free to contact me of you have any questions.

Many thanks,

[IBazz]

erm, is that not the same as the adjacency list model? That's what I thunk I used (different app), and it is pretty much the same.

[r937]

yes, bazz, it is

DatabaseDesigner just offered another example of the same structure we've been talking about all through the thread

[DatabaseDesigner]

Hi guys. Yes, you are very right. I did just provide the same as you guys.

This was my first contribution on a forum, and I suppose I got a little giddy and a little too eager to reply, so didn't read through the thread properly.

My bad...

As a positive, at least it backs you up?

Cheers

[r937]

not a problem, and welcome to sitepoint

your remark about not needing more than three levels for this scenario is right on the money, by the way

too often i've seen people throw their hands up at the adjacency model and say "well i can't use it because i have infinite levels" when in fact they come nowhere close to pushing the envelope on self joins

we once had a thread here where the efficiency of multiple self-joins was questioned, and one guy ran a test, demonstrating that the adjacency model had excellent performance for up to 15 levels deep

now i ask you, isn't there a bigger problem there? i mean, how are you going to display that many levels to users, and not have them get lost?

[DatabaseDesigner]

To display that many levels would be a nightmare, and would only make sense in a tree style format. Even that would be messy...

Although this technique has (in theory) only hardware limitations for hierarchical potential, we should use as few levels as possible if it is to be human friendly... And for Altr's purpose, 3 levels should be the "aimed for" tops. I'm basing this on a web developers point of view where the user should be able to get anywhere they want on a site within 3 clicks. Not always possible, which is why we suggest this model as it can cater for that.

ps. Thanks for the welcome.

[atlr]

May I suggest the use of a Unary relationship to handle your parent/child relationships?
Put simply, your table would have its usual id (primary key) and a foreign key maybe named PID or parent_id (whatever your preference. The foreign key will represent a record within the same table. Example:
ID = 1, PID = NULL, name = home
ID = 2, PID= 1, name = company
ID = 3, PID = 2, name = mission statement
ID = 4, PID = 1, name = login

Looking at this example, we can see that the first record is the home page, the mother page. This will not have a PID as it is the highest level and has no parent.
The second record has a PID of 1 to show that this record is a child of the home page.
The third record has a PID of 2 making the second record the parent of this one.

This technique is ideal for what you are after as it means you don't need multiple tables to accommodate the different levels in your hierarchy, and the hierarchy is unlimited. Although it is highly unlikely that you would go any deeper than maybe three levels, you have the luxury of having unlimited hierarchical scope.

Does this make sense?

Please feel free to contact me of you have any questions.

Many thanks,

Hiya,

Thanks for the replies - I've been working on a few other areas of the project for the last few days, and forgot to check back here !
Annyway.

@DatabaseDesigner - this is exactly the structure I've used for the db. Hurrah!
The problem I've now got is how to get this to output.

I've got a controller script, and then a display script (as taught by the fabulous Kevin Yank )

I've got the following code in my controller script

	////////// Build Menu //////////
 
	include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';  
	$select = "SELECT parent_id, name, link_title FROM pages WHERE published='on'";
 
	$result = mysqli_query($link, $select);
	if (!$result)
	{
		$error = 'Error fetching page structure, for nav menu generation.';
		include 'error.html.php';
		exit();
	}
 
	while ($row = mysqli_fetch_array($result))
	{
		$nav_links[] = array('parent_page' => $row['parent_id'],
							'name' => $row['name'],
							'link_title' => $row['link_title']);
	}

and in my display script, which is included into the controller script, I've got:

<?php foreach ($nav_links as $nav_link): ?>
 
<?php 
 
	if($nav_link['parent_page'] == '0' || 'null') {
		htmlout($nav_link['name']);}
 
?>
 
 
 
<?php endforeach; ?>

I've not put any UL or formatting stuff in there yet, as I need to get the data outputting from the DB correctly first, before I start styling it, etc.!

I know I'm missing some code here, but what I'm trying to do is get each parent to output, with its children and its children's children all there, in a UL-style output. I'll then turn this into a drop-down for the frontend, but am unsure how to get each to output!

Any help, as ever, gratefully appreciated

Al

[r937]

... what I'm trying to do is get each parent to output, with its children and its children's children all there, in a UL-style output.

see the article linked in post #2

[atlr]

see the article linked in post #2

Thanks for this. The problem I'm having is actually achieving the UL-style output. So far as I can see, there's no guidance about how to do this or how to code the while loop. The reason I've posted again is because I'm still unsure, not because I've been lazy and not read the article.

Al

[IBazz]

check out the php/perl forums as appropriate. there is I am confident, a ton of examples of others' questions on the same thing.

y'see, php is (amongst other things), for outputting what the query has retrieved from the db. so at that stage it isn't a MySQL issue so you shuold get a quicker and possibly better answer in one of the server-side scripting forums.

bazz.

[atlr]

Thanks Bazz.
Just FYI, I've posted here (linky) as I guess you're right - this thread has addressed my database design query - and I've settled on the adjacency model - but my problem now is in the successful extraction of that data using PHP.

Thanks for all your help, though

Al