Bob,
Your options are: 1) Include a TEXT datatype on every row. This eats up a lot of space without need. 2) Include a NOTE datatype on every row. This is less demanding of space, but carries the issue of the relocation of rows when the note grows beyond a certain point. 3) Include a VARCHAR datatype on every row. This is less demanding of space in file 2, and avoids the row relocation issue with NOTE types, but will consume space in file 4 for every row. 4) The linked table with the datatype of your choice. Personally, I’d opt for 4. We use that approach for comments on a bill of lading, for example, with great success. You only take up space when you require, and you can use whatever datatype you wish. Emmitt Dove Manager, Converting Applications Development Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Wednesday, December 16, 2009 6:05 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Note Data type question Paul, I probably was not very clear. The data in this table will be accessed constantly throughout the day and any comments will always be brought up at that time as well. Any reports would always contain the comments also. So the "Comment" table would be accessed every time the main record was. There may not be any comments for the record, but a check would have to be made anyway. Thus I do not think the traffic will be lower as the program will have to access the second table on every inquiry, update ,etc. Even if no comment is linked to a record, any program would have to check to see if one was. So I believe the traffic will actually be higher with option B. I would have agreed with you 100% when thinking about this "off the cuff" so to speak. But after thinking about it for a while, I am thinking with the over head of a second table, I am beginning to think otherwise. It is said that a good database always starts with a good design. So I have been attempting to put more thought up front in the small details for over all performance and functionality. Sometimes performance and functionality are at odds with each other. (Not always) I am thinking that option A in this case might be the better choice, unless there are consequences about using a NOTE data type on the main table. Thanks again, -Bob Bob Option B! less traffic unless the user calls/wants to see it. My.02 Paul D. 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 -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Wednesday, December 16, 2009 5:42 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Note Data type question Bob: Don't know if your email came thru this way for everyone, but below (hopefully) is what your email looked like to me! Karen
