RTFM tables design, and good FAQ structure

RT Users
1

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