CMS: synchronize links to internal pages in page's content

Question

I've made this modest CMS in PHP (and Zend Framework).

The content of a page of the published website is stored in a MySQL TEXT field. When editing the content of a page, one is able to create links to other internal pages within the website.

The pages slugs (SEO uri's) are also editable in the CMS.

What is a good way to synchronize internal links in content, when a page's slug that is linked to is altered by the end-user of the CMS?

I welcome solutions, from simple and robust solutions (without too much refactoring of the database), to more complex and flexible, that need some refactoring of the database and such. What are some good ideas to handle this?

Thanks in advance.

PS.: It doesn't have to be specified in PHP/MySQL. Just the global concept will probably do fine, unless it is something obscure that PHP/MySQL is unable to handle of course.

Answer 1

Option 1:

Search the content for slug URLs when inserting/updating content, and store them in a table that relates slug urls to their id's, and to the content id. ('slug_content_map')

You can then add a method that replaces the old slug URL with the new slug URL in the content when the URL is updated, if the content has an entry in 'slug_content_map'.

Option 2:

When inserting/updating content, replace slug URLs with a string such as {slugId:3} , and then replace that dynamically when displaying the content. This method has more overhead when displaying the content as you'd have to lookup the slug to replace it.

Answer 2

Here's how some content management systems approach it, and in general it seems to be the best balance as far as runtime performance, edit-time performance, and manageability:

Keep a table of pages and links:

|PageID    |LinksToID    |
|----------|-------------|
|1         |2            |
|1         |3            |

1. When you store page (foo), find all the links in that page (not very intensive), find where each one links to (trivial, since of course you have your pages indexed by URL, right?).
2. Populate this table with that info (page 1 has links to pages 2 and 3).
3. When page 3 is renamed, go back to this table and pull all pages that link to it; search their content for URLs to page 3, replace and update.

No runtime impact, and the edit-time impact is optimized. And you get a handy, easy-to-query map of all the cross-links in your site. Bonus!