I believe for true optimize db, I would still opt for “B” “besides your statement, “There may not be any comments for the record, but a check would have to be made anyway.”
But if there is a “If” and NOT always and even then 100% of the time I tend to say “B” . This of course is my preference and at this moment, I am in the same exact process and now making more tables in one db then I ever have done, past/present. However if a table somehow gets zapped so to speak! What then? I believe comments are optional, verses the rest of the information. Now that does not say comments are not important, but they are recoverable from backups. Perhaps all of it is. But I worked on a table back in the past, for 2 days we recovered that table and that was 2 people (RBase) working. Had those comments been outside of the table, the db would have gone on. Take notes and add the comments later in the db, instead it was a mad dash to the finish line. I have since tried to optimize all tables/db. If you create a report and that report has no field for “comments” and you run this report I believe ALL data is passed regardless. So all those comments that are not going to show on the report, still are taking the time to process (as in packets/cpu time). Now I believe this is true, I bet someone will confirm this, which knows it for a fact. Till then I hold ‘B’ is best. When working with 50k rows + My.02 Sincerely, Paul Dewey 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
