DB design document

[Dave Peticolas]

David Merrill writes:
> 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.

Ok, I guess postgres's 'text' field (arbitrary length text) isn't very
common. That is too bad; I dislike putting limits on user-entered text
fields.


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

We may have to gather some usage information -- I have no idea what
people typically use, but 50 seems a bit small.

dave