[rt-users] RTFM tables design, and good FAQ structure

[Paul Nesbit]

RT is 3.0.10
RTFM is 2.0.4

I've not been able to qeury the RT FM_* tables the way I was
expecting I could when I went about designing my FAQ several months
ago.  Now I'm second-guessing the manner in which I've structured my
FAQ, and how I should (or can) present it (using the RTFM UI
itself isn't an option).  I'm curious how others have implemented
custom fields and custom field values, and perhaps if a design
change in the FM tables would be useful. 

Here's the dilemna -- I've been building a FAQ with the following
structure:  

A Class called "FAQ".  
A FAQ Class CustomField (type SelectSingle) called "Category".  
A set of Category CustomFieldValues.  

Now I'd like to publish my FAQ, and present all the articles divided
into CustomFieldValue categories.  E.g.

   Category: Lifecycle Phases
     article 1
     article 4

   Category: Troubleshooting
     article 3
     article 9

The problem with my structure is that there is no tie between
articles and category values, so I can't write an SQL statement to
say 

  'select all articles where category is "Troubleshooting"'

The FM_ArticleCFValues.CustomField key contains the value of
FM_CustomFieldValues.CustomField, not FM_CustomFieldValues.id.  So
the query becomes 

  'select all articles where customfield is category, and category content is "Troubleshooting"'

It seems that the FM_CustomFieldValues.id primary key would be a
better candidate to tie the two tables together, so if changes to
the Category definitions were required in the future, I could change
them without worrying about updating the faq-generation code as
well. 


  mysql> describe FM_ArticleCFValues; 
  +---------------+----------+------+-----+---------+----------------+
  | Field         | Type     | Null | Key | Default | Extra          |
  +---------------+----------+------+-----+---------+----------------+
  | id            | int(11)  |      | PRI | NULL    | auto_increment |
  | Article       | int(11)  |      |     | 0       |                |
* | CustomField   | int(11)  |      |     | 0       |                |
  | Content       | text     | YES  |     | NULL    |                |
  | Creator       | int(11)  |      |     | 0       |                |
  | Created       | datetime | YES  |     | NULL    |                |
  | LastUpdatedBy | int(11)  |      |     | 0       |                |
  | LastUpdated   | datetime | YES  |     | NULL    |                |
  +---------------+----------+------+-----+---------+----------------+

The FM_ArticleCFValues.CustomField points to a
FM_CustomFieldValues.CustomField item, not FM_CustomFieldValues.id.

  mysql> describe FM_CustomFieldValues; 
  +---------------+--------------+------+-----+---------+----------------+
  | Field         | Type         | Null | Key | Default | Extra          |
  +---------------+--------------+------+-----+---------+----------------+
? | id            | int(11)      |      | PRI | NULL    | auto_increment |
* | CustomField   | int(11)      |      |     | 0       |                |
  | Name          | varchar(255) |      |     |         |                |
  | Description   | varchar(255) |      |     |         |                |
  | SortOrder     | int(11)      |      |     | 0       |                |
  | Creator       | int(11)      |      |     | 0       |                |
  | Created       | datetime     | YES  |     | NULL    |                |
  | LastUpdatedBy | int(11)      |      |     | 0       |                |
  | LastUpdated   | datetime     | YES  |     | NULL    |                |
  +---------------+--------------+------+-----+---------+----------------+
  
Have I structured my FAQ in a silly way? 

Am I right to think the FM_ArticleCFValues.CustomField should point
to FM_CustomFieldValues.id?


Thanks,

  Paul