I would like to ask for input about using the NOTE data type in a table and what the thoughts are on the pros and cons.
I am developing a system that will generate a table with a significant number of rows, probably averaging around 50,000. There will be 10 data columns consisting of date, integer and text. The total text character counts for these columns will be 62. So the table records are relatively small. I am estimating that about 60% of the 50,000 records will have need of a comment field which needs to be up to 250 characters. Often much smaller only 10 -30 characters, but occasionally a much more detailed comment that would require much more space, thus the 250. This table will have significant number of updates and new rows added on a continuous basis. So the two obvious options would be : A: Use a NOTE type data field in the table and carry the comments with each record in the table. B: Create a separate table to hold the comments and link the records by a common ID. Option B would use slightly less disk space, but that is not really of a concern and I believe the savings would be minimal. I believe it would be less efficient speed wise as multiple tables would have to be addressed for each data manipulation command, report generation and possible searches. The index file would grow larger due to the required additional index on the ID field of the COMMENT table needed to improve processing speed. This might slow down other table operations to some degree. (This database is heavily accessed by several users throughout the day) Option A in this case would seem to be the most efficient processing wise. However I believe some hesitate to use NOTE data types on larger tables. I have constructed databases for quite some time and normally use the second table approach. But I have been thinking that in some cases, such as this one, it might not be the best method. I would appreciate any thoughts and input on the subject. Thanks, -Bob
