Handling multiple free form text comments

[flabbott]

I just read the following thread regarding how to handle free form text comments.
http://forum.kimballgroup.com/t784-modelling-free-text-comments

Based on this thread I plan to create a comment dimension. However since I have many comments for one fact record, should I create a bridge table? This seems a bit complex. The comments could contain anything. Some clients might use them as intelligent fields for filtering. Others may just use them for notes. The current max size for each comment is 250 bytes.

Thanks!

[ngalemmo]

In this particular case, a bridge would overcomplicate things with little or no benefit. This has to do with the nature of the data (free form) and its potential size. I would 'cheat' and deviate a bit from the standard form. I would probably assign unique surrogate keys to fact rows and store the comments in another table with the fact row key as an FK in the comments table, essentially a bridge table with the comment as a degenerate dimension value. Short of putting structure to the table by parsing the comments, this will support any type of query pattern. It will perform about as well as you could expect a query against free-form data would perform in a relational database.

[flabbott]

That's what I actually did, but knew I was breaking the rules and wanted to make sure I was right. Thanks!

[Sponsored content]