DB design document

[David Merrill]

On Wed, Dec 13, 2000 at 06:14:31PM -0800, Dave Peticolas wrote:
> David Merrill writes:
> > Please take a look at the latest revision of my design document and
> > give me some feedback.
> > 
> > Some of the issues I'm dealing with:
> > 
> > 1. Architecture: A big, fuzzy cloud right now.
> > 2. Security: Lots of questions, few answers. Among the questions,
> > encryption over the wire, locking down the db itself (depends on the
> > db), and managing user permissions on some kind of capabilities model
> > tbd.
> > 3. Table Schemas: Gaping holes.
> 
> 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.

You can't avoid having a limit on text fields, but you can make them
very large.

Most databases have a 'char' type, which is essentially a fixed length
string, and it always takes up length+overhead space in the database,
regardless of how many characters you enter in it. This is faster when
most strings are near the full length, for example for GUIDs.

Then you have 'varchar' which is a variable length char. It takes up
the number of characters+overhead. But the overhead is greater. It uses
less space in the database when the length varies greatly, so it's
good in that sense, but it is slower to index.

So we will use varchar fields, and make them fairly large. At your
leisure, please review my schema and suggest maximum data sizes.


-- 
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

A political man can have as his aim the realization of freedom,
but he has no means to realize it other than through violence.
		-- Jean Paul Sartre