Page URL and database organization.
- by shurik2533
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?
