Steve, I think someone else was talking about the broken pointer issue ... However, I believe it was the risk of this, associated w/defining NOTE-type columns in what would otherwise be a table of a fixed (record) length - which most ALL tables should be, subsequent to proper normalization and prior to any known/ad hoc de-normalization "cheats" - that first led me to consider the approach of creating a separate table JUST f/NOTE-type data (usually/alway) associated w/a (dominant) "sibling" record, i.e. 1:1 in the "other" table. Upon further learning and analysis, this ALSO seemed to be the ONLY way to go to adhere to the rules of NORMALIZATION, but here I could be wrong.
Anyone, if this is correct, do we therein approach a 4th Normal Form subset w/in our 3NF/DKNF database? I some rusty on the details, but, 1) I think NOTE-type fields defined in tables are stored in RB2 2) I think this is true, but there is some internal "overhead" associated w/every row/column 3) Umm, it's been a long time since I had a broken pointer, in any kind of table. ) R:Scope is a great tool f/that situation, esp. if data appears to be lost. ) As I said at the beginning ... Also, although I'm glad to help, if this does indeed help you, please don't accept my info as gospel, as I all too often use anecdote and opinion when I should be researching the facts ... I need to try harder, but maybe this helps you get pointed in the right direction. Later, Steve in Memphis ----- Original Message ----- From: "Fogelson, Steve" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, September 17, 2003 12:38 PM Subject: [RBASE-L] - RE: Note Fields > Albert, Alastair, and Steve, > > Great information and suggestions. > > Couple more questions? > > 1) Is a note field stored in .rb2 with the rest of the table or does it go > in rb4 like varchar? > > 2) I assume that the note field only uses disk space for the number of > characters entered unlike a text field where it is allocated whether used or > not. Is this true? > > 3) What happens if you have a broken pointer in the NOTE table? How do you > fix that? With R:Scope? Is this what you were referring to Steve? > > Thanks > > Steve > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 17, 2003 11:21 AM > To: [EMAIL PROTECTED] > Subject: [RBASE-L] - RE: Note Fields > > > Fixing broken pointers requires RScope to find the next row of data for the > table after the point where the broken pointer is found. To do that, RScope > looks for rows of equal length to the defined length of the table rows. > RScope cannot do this with note fields because the row length is variable. > > There is a note table matching every table that needs note fields. This > table has the PK from the header and the note field only. If we assume 10 > tables with note fields, after the redesign, there will be 20 tables. > > A further consideration is that a note field is up to 4,092 characters while > the maximum row length is 4,096 bytes, leaving room for a single integer > value at 4 bytes. This could mean more than one note field per table, or > more than one note table per master table. In this case, one should > carefully consider using varchar fields, which are stored in file 4. In this > case the row length of the master table is fixed. It also allows more than > 4,092 characters to be stored. > > Hope this thins the mud out a bit. > > > "Fogelson, Steve" <[EMAIL PROTECTED]> wrote: > > >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 > > > > > > > -- > Albert Berry > Full Time Consultant to > PSD Solutions > 350 West Hubbard, Suite 210 > Chicago, IL 60610 > 312-828-9253 Ext. 32 > > > __________________________________________________________________ > McAfee VirusScan Online from the Netscape Network. > Comprehensive protection for your entire computer. Get your free trial > today! > http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 > > Get AOL Instant Messenger 5.1 free of charge. Download Now! > http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 >
