powerhouse has asked for the wisdom of the Perl Monks concerning the following question:

Ok, this is a combination of a Perl question, and Database question.

I just want to know what YOU would do...

I'm making in my wifes admin interface a way for her to edit her meta tag keywords AND description.

Should I put it like this:
-------------------------------------------
|          meta_table_page_info           |
-------------------------------------------
| id  |  page   |   keywords   |   desc   |
-------------------------------------------

[download]
Should I put all the keywords in the ONE column, OR should I create a new record for EVERY keyword, and add a column named "seperator" where she can tell it how to be seperated and it would just do something like: 
while($trow = $sth->....
$keywords .= $trow->{keywords} . $trow->{seperator};
}

[download]
Then it would be harder to have a description, in there, because there would be multiple records for that ONE page, so, I would have to create ANOTHER table to hold the description.

I am using Perl, so I have CGI.pm use the start_html(-meta=>{'keywords'=>$keywords});

Anyways, What would YOU do?

Thanks,
Richard

Replies are listed 'Best First'.
Re: Seeking YOUR Wisdom... CGI.pm, Database and YOU
by jgallagher (Pilgrim) on May 01, 2003 at 21:26 UTC
    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   |
---------------------------

    [download]
    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 |
--------------------------

    [download]
    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       |
---------------------------

    [download]
    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

    [download]
    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.
[reply]
[d/l]
[select]
      WOW, you've out done yourself!!

      Thank you, that is perfect!

      thx,
      Richard
[reply]
        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.
[reply]

Back to Seekers of Perl Wisdom