Storing comments in a SQL database

[dmorse]

I have written a program that allows people to evaluate themselves based on 10 criteria and raters to also rate them. There are many comment fields that I need to store. What is the best way to store these comments. I have one person storing 2500 characters in comments. Any suggestions would be greatly appreciated.

 

[mp9]

For such long comments, you need to use a Memo data type. Be aware that Memo types have limitations though. For example, in A2002 you can sort or group by a Memo field but Access only uses the first 255 characters to do so.

http://www22.brinkster.com/accessory/

 

[MajP]

Not sure if this is what you are asking, but here is how I would set up my tables.

tblPersonnel
personnelID
otherFields related to personnel

tblCriteria
criteria ID
criteria Description
other Criteria related fields

tblComments
comment ID
memoComment (memo field as mp9 said)

Now here is the trickier part. Make what I call a join table for the many to many relationship.

joinTblRaterCriteriaComment
foriegnKeyRateeID (from Personnel Table)
foriegnKeyRaterID (from Personnel Table)
foriegnKeyCriteriaID (from Criteria Table)
foriegnKeyComment (from Comment Table)

Now you have a relationship for every person rated, who rated them, angainst a specific criteria, using a specific comment.

Hope this helps.

The join table can now relate each

 

[dmorse]

Is there still a limit on how big the memoComment can be? Sorry it took so long to get back, been crazy here. I appreciate any help that can be given.

 

[Golom]

Yes ... memos are limited to 64,000 characters.

 

[dmorse]

Please forgive my ignorance....I thought I had a problem with my SQL database recordsize being too big after it reached over 8000 characters. Am I missing something?

 

[Golom]

Internally Access doesn't store a memo field in the record. It stores a pointer to the memo field's address. That's the fundamental reason for the limitations on memo fields that mp9 mentioned.

 

[dmorse]

Since I have them currently stored as a vaarchar, is there a way I can successfully convert these fields over to memo fields? (Sorting and grouping is not an issue, because they are truly just employee comments).

And also, if I am writing from a VB6 program to the memo field in the SQL database, is there anything special I have to do since that is a pointer, or does SQL take care of that automatically?

 

[Golom]

ALTER TABLE myTable ALTER COLUMN myColumn Memo

Other than the limitations (256 characters; cannot index a memo field), Access will take care of entering and displaying memo data.

But I'm a bit confused. Are you using Access or SQL Server?

This is the Microsoft: Access Queries and JET SQL Forum. If you are using SQL Server then you probably want to ask in forum183 Microsoft SQL Server.

 

[dmorse]

SQL with a VB 6 interface.

 

[Golom]

From BOL

varchar [ ( n | max ) ]

Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length.

But the real experts are in forum183