Comments Table Used For Multiple Types of Comments

[goldcougar]

I want to be able to allow people to enter comments into a comments table through a portal. However we have many different types of Comments. Lets say Service Comments, General Comments, and Sales Comments.

So, I want 3 different layouts showing records from a table called Case, with portal records showing comments. 1 Layout shows portal records of Service Comments, the other General Comments, and the other Sales Comments.

What is the best table structure to implement that?

Do I need a different table for each type of comment, or 1 comment table with a 'comment type' field?

[smorr]

I have the exact same situation in a solution I have developed. What I did was 1 table with a commentTypeField.

For portals, I created on the leftside table "constant" fields that match the comment types allowed. (calcuated fields with text result equalling the commenttype)

eg

k_COMMENT_TYPE_SERVICE = "SERVICE"

k_COMMENT_TYPE_GENERAL = "GENERAL"

k_COMMENT_TYPE_SALES = "SALES"

Then any portal relationship just includes the comment type constant relationship as a extra key

eg:

TABLE A <-------------------> TABLE B

    k_COMMENT_TYPE_SERVICE = _Comment_Type

and k_STATUS_OPEN          = _Status

and #curServiceRecord_ID   = _ParentRecord_ID

if you want to show comments of more than one type in a portal, there are two ways of doing it.

first way is to create a multiline constant with the comments you wish to show.

eg:

k_COMMENT_TYPES_SALES_SERVICE = "SALES

[transpower]

You could have four tables: Case, Service Comment, General Comment, Sales Comment. The primary key in Case would be CaseID_pk. The foreign key would be CaseID_fk in the other tables. Each person would create a new record for each comment and would fill in the CaseID_fk. The tables would be linked by CaseID. There would be three portals shown on the Case layout. You could add new comments in the portals directly or on the layouts of the comment tables.

Frankly, this seems rather more elaborate than you may need. You could just have three text fields for notes on the case; add vertical scrolling bars.