Adding to what Albert said about RB in particular, this approach (TTBOMK) is not exclusive to any implementation-choice (RB, DB2, SQL Server, Et Al.). Additionally, there is also (sometimes) a certain amount of storage efficiency gained by this approach, esp. if the choice of field-data-type, NOTE vs large/long TEXT, is an issue.
F/example, we do lots of survey-based research, wherein we have Questions that can allow an INTEGER-type response, like 1..10 (HATE..LOVE), and 11, "Other - Please Describe", to handle a response that is not in the pre-defined solution domain. This type of "Other" then invokes a procedure to allow an open-ended text response, but only when it's indicated by that type of response. If the field were stored in the same table w/the INTEGER responses, even as a fixed-length column, EACH & EVERY record would have it "instantiated", even though the vast majority would have NULL-values therein. I have also found a curious thing about NOTE-type fields that might be resolvable by way of "SET NOTE_PAD", but I went back to a TEXT (1024) column definition. In the app here, I navigate a cursor thru a (TEMP/DRIVER)table wherein the ID(PK) f/the row is also the SEQUENCEr and therefore must remain static. I found that when I had NOTE-type columns in these tables, that the addition of too much data would flag the previous row f/DELETE-ion and APPEND the modified row to the end of the table. Then, the next pass thru the table would terminate processing at whatever record had been moved to the (physical) end of the table. As I'd encountered this logical/practical approach to row UPDATEs in the past, it didn't take long to figure out that a (probably short-term) remedy was to "fix" those open-ends at some value large enough to hold 99%+ of the responses but not ridiculously large, thus 1024, and, as I was under a lot of time-pressure, it was easy to do. This (possibly) oddball implementation is also acceptable in practice here, as the largest number of records in one of these TEMP/DRIVER tables has been around 500 f/a project we no longer have ; most of the record counts are in the 20-30 per table range. If this doesn't make any sense, just say so, and I'll try to elaborate/explain differently or I'll just say that some folks think I'm a bit crazy. Others have even gone beyond the implication that I am a redneck, but that's another story. HTH, Steve in Memphis ----- Original Message ----- From: "Fogelson, Steve" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, September 17, 2003 11:09 AM Subject: [RBASE-L] - Note Fields > A while back I had asked about Text vs. Note fields. > > A few responses indicated that they keep all "note"s in a separate table. > Evidently problems with broken pointers. > > I assume you design your DBs with a table for ALL notes. And all the other > tables contain Note_ID fields where appropriate, that point to that note in > the note table. Then use a view to read a row including the note. > > Are these assumptions correct? > > Could someone elaborate on this design and problems with broken pointers. > How is this design strategy easier to fix broken pointers? > > Thanks > > Steve Fogelson > Internet Commerce Solutions >
