in reply to Seeking YOUR Wisdom... CGI.pm, Database and YOU

You asked what I would do, so take this with a grain of salt, of course. I'm assuming that there may be some reuse of keywords, so what I would do is normalize the data (note that I am far from an expert in database design, but I found this article to be a pretty good introduction to the ideas of normalization) like so:
--------------------------------------------- | meta_table_page_info | --------------------------------------------- | page_id | page | keyword_separator | desc | --------------------------------------------- -------------------------- | keywords | -------------------------- | keyword_id | keyword | -------------------------- --------------------------- | page_to_keyword_mapping | --------------------------- | page_id | keyword_id | ---------------------------
With this scheme, you'll put all the keywords you'll use for all the pages in the keywords table, the separator you want for each page in the keyword_separator column of the main table, and the mapping of keywords to pages in the third table. An example... say you have:
-------------------------------------------------------------- | meta_table_page_info | -------------------------------------------------------------- | page_id | page | keyword_separator | desc | -------------------------------------------------------------- | 1 | some_page | , | some_description | -------------------------------------------------------------- -------------------------- | keywords | -------------------------- | keyword_id | keyword | -------------------------- | 1 | keyword_1 | | 2 | keyword_2 | | 3 | keyword_3 | --------------------------
and you want page 1 to have keywords "keyword_1,keyword_3". Then the mapping table would look like:
--------------------------- | page_to_keyword_mapping | --------------------------- | page_id | keyword_id | --------------------------- | 1 | 1 | | 1 | 3 | ---------------------------
To build the actual string, you'd use a SQL statement in the vein of
SELECT m.keyword_separator, k.keyword FROM keywords k, page_to_keyword_mapping p, meta_table_page_info m WHERE p.page_id = ? AND m.page_id = p.page_id AND k.keyword_id = p.k +eyword_id
Then you execute(page_number), and go from here. This may be far more complicated than you were wanting, but designing it like this leaves lots of room for expansion.

Replies are listed 'Best First'.
Re: Re: Seeking YOUR Wisdom... CGI.pm, Database and YOU
by powerhouse (Friar) on May 01, 2003 at 22:21 UTC
    WOW, you've out done yourself!!

    Thank you, that is perfect!

    thx,
    Richard
      Always glad to be of service. :-)

      If you want to learn more about the ideas behind that, I strongly recommend the article I linked to above. It was a light coming on inside my head when I read it.