Page URL and database organization.

Question

I want that its name would be the page address. For example, if page has heading "Some Page", than its address should be http://somesite/some_page/. "some_page"-name generated by system automatically. "some_page" - is the unique identifier of page. The problem in that the user in the future can enter a name which already exists that will cause an error.

It is necessary to find an optimum variant of the decision of a problem for great volumes of the data.

I have solved a problem as follows: The page identifier in a database is the name of page and a suffix which is by default equal to zero. At page addition there is a check on existence. If such page does not exist, the suffix is equal 0 and its name is "some_page", if page is exist, than - search for the maximum number of a suffix and suffix=suffix+1 and page name become "some_page_1".

For this I create in a database the compound key from fields "suffix" and "pageName":

Table Pages

suffix|pageName  |pageTitle
0     |some_page |Some Page
1     |some_page |Some Page
0     |other_page|Other Page

Addition of pages occurs through stored procedure:

CREATE PROCEDURE addPage (pageNameVal VARCHAR(100), pageTitleVal VARCHAR(100)) 

BEGIN

    DECLARE v INT DEFAULT 0;

    SELECT MAX(suffix) FROM pages WHERE pageName=pageNameVal INTO v;

    IF v >= 0 THEN

        SET v = v + 1;

    ELSE

        SET v = 0;

    END IF;

    INSERT INTO pages (suffix, pageName) VALUES (pageNameVal, v, pageTitleVal);

END;

Whether there are more the best decisions?

Answer 1

I think this should be okay - it would keep multiple instances of the key distinct. However, why not use a generated key instead of something provided by the user? If you maintain control over the page's lookup ID, you'll ensure no duplicates. Your current setup shouldn't cause any trouble, though.

The only problem (though it seems highly unlikely), is that your SP has a chance to duplicate the suffix for a particular key if two callers try to save the same key at the same time - ie, two simultaniousrequests with the same brand new pagename could both end up trying to use suffix 0. If you don't care about the result of your insert statement (and your current SP doesn't return it), then just do it in a single statement inside your SP:

CREATE PROCEDURE addPage (pageNameVal VARCHAR(100), pageTitleVal VARCHAR(100)) 
BEGIN

    INSERT INTO pages (pageName, suffix, pageTitle)
    SELECT n.pageNameVal, ISNULL(NextValue, 0), n.pageTitleVal
      FROM (SELECT pageNameVal, pageTitleVal) n
      LEFT
      JOIN (SELECT MAX(suffix+1) as NextValue FROM pages WHERE pageName=pageNameVal) m

END