DB design document

[Patrick Spinler]

Unfortunately, David Merrill is right.  Is is difficult at best to store
data of arbitrary length in a database.  This is a large part of the
reason why BLOBs were introduced.  BLOBs have their own drawbacks,
though, like not being able to index and search on them.

The best you can do in a "traditional" rdbms is to chop the data into
fixed sized chucks, and associate some arbitrary number of chunks with
the base table.  It's pretty hacky, though.  Something like this: (not
syntax checked!)

create table a 
  (
  an_int   int,
  text_key int
  )

create table b
  (
  a_float  float,
  text_key int
  )

create table text_store
  (
  text_key int,
  text_seq int,
  data     varchar(40)
  )
 
So, to store an "a" value like this:

  1, "a very long string of text that blows out a column limit"

Would look like:

  a:
  an_int |text_key
  -------|--------
  1      |01AF

  text_store:
  text_key |text_seq |data
  ---------|---------|----------------------------------------  
  01AF     |1        |a very long string of text that blows ou
  01AF     |2        |t a column limit

I constructed this example to show that multiple tables could share the
same "text chunk" data store (assuming that the "text_key" values are
uniquely assigned).  However, it is often schematically clearer to have
a dedicated "text detail" table for each master table, or even for each
text column in each master table.

As you can see, it would be, um, somewhat more difficult to use this
type of structure regardless of the exact schema implementation. 
Sometimes you really want to have arbitrary values, though.

-- Pat

David Merrill wrote:
> 
> On Sat, Dec 16, 2000 at 09:18:39AM -0600, Rob Browning wrote:
> > Dave Peticolas <dave@krondo.com> writes:
> >
> > > Anyway we can avoid putting arbitrary limits on the lengths of text
> > > fields like account names & such? Currently gnucash imposes no such
> > > limits and I think it would be good to keep it that way. I know
> > > postgres has arbitrary length text fields, but I don't know about
> > > others.
> >
> > I agree with Dave completely.  (This is on my list of things I'm
> > accumulating that I want to suggest when I finish catching up on this
> > thread.)  Although I suspect that whether or not we can support this
> > easily depends on whether or not we're willing to (a) limit ourselves
> > to a particular DB, (b) have "per installation settings" that allow
> > you to select arbitrary lengths if you're willing to pay the price (or
> > perhaps limit yourself to a particular set of DB's), or (c) plan to
> > depend on a server-side proxy that can handle the messy details of
> > storing arbitrary length strings (if that's even possible) in DBs that
> > don't handle them natively.
> 
> I'm going through the schemas this morning and enlarging all of those
> fields. If I miss something, let me know.
> 
> Unfortunately, however, allowing *very* large text fields has serious
> drawbacks in databases that it does not have in XML. You pay a price
> in terms of storage space and indexing, which translates to query
> execution speed. Not just on the record with large entries, but on all
> of them.
> 
> --
> Dr. David C. Merrill                     http://www.lupercalia.net
> Linux Documentation Project                dmerrill@lupercalia.net
> Collection Editor & Coordinator            http://www.linuxdoc.org
>                                        Finger me for my public key
> 
> I give the knowledge of the spirit eternal
> And beyond death I give peace and freedom
> And reunion with those that have gone before.
>                 -- from The Charge of the Goddess, Doreen Valiente
> 
> _______________________________________________
> gnucash-devel mailing list
> gnucash-devel@lists.gnumatic.com
> http://www.gnumatic.com/cgi-bin/mailman/listinfo/gnucash-devel

-- 
      This message does not represent the policies or positions
	     of the Mayo Foundation or its subsidiaries.
  Patrick Spinler			email:	Spinler.Patrick@Mayo.EDU
  Mayo Foundation			phone:	507/284-9485