I have created a coldfusion webpage form that writes to a sql database. For comment boxes they can enter as much text as they need. Do I want to choose a varchar for my datatype and put a riduculously high number for characters or should I should use a text datatype and if I use text are there specific options I should set up?
Thanks!!!
Dex:
It depends on which version of SQL Server you are using; if You are using SQL Server 2005 use VARCHAR(MAX) instead of TEXT.
IF you are using SQL 2000, you can use a text datatype -but working with it will be a bit more troublesome. Searching within the comment will require setting up FULL TEXT Search (a separate product), and the indexes for FULL TEXT search are not dynamic and may be out of sync with the last changes.
In either SQL 2000 or SQL 2005:
My recommendation is to have a [Comments] table, using a varchar() datatype (generously sized for a 'normal' comment), and each comment becomes a separate row in the table, linked to the record by the appropriate PK-FK, and each comment can easily be marked with the datetime, user, and/or any other information that would be useful. The comments records can then be easily displayed in ASC/DESC order, searched, and by adding flags to the table, it becomes possible to have some comments that are not visiable by all users.
