String lengths in the SQL backend
i at idiosyncra.tc
Thu Nov 13 01:24:44 EST 2008
On Wed, Nov 12, 2008 at 6:11 PM, Derek Atkins <warlord at mit.edu> wrote:
> Quoting Eric Anopolsky <erpo41 at gmail.com>:
>> I'm not familiar with gnucash's data structures so I'm not 100% sure
>> what a "code" is, but would it be possible to identify each account by
>> an int or a bigint instead of a code? Then this secondary table could
>> have the "account int" as its foreign primary key, and you could have
>> another table that maps "account int" to "code" with the code as a
>> varchar(2048) as desired.
>> Or put the "account int" and the "code" in the same table and make the
>> int the primary key instead.
>> Totally unreasonable?
> Uhh, yeah. Sorry. Totally unreasonable.
> The "code" in question is a user-input field. Historically accountants
> used codes instead of names to keep track of accounts. So GnuCash provides
> a place for you to enter in an Account Code. But it's a string, not a number.
> Granted, most users probably do only use numbers, but there is no
> requirement that it be a number.
As a GnuCash-naive, but MySQL-adept user, I would have recommended the
same thing as Eric. The point being: never use user-input fields for
keys. Sure, code needs to be a string because users want to be able to
enter anything into it. So don't use it as a key. I doubt that's
*totally* unreasonable, though I suspect it's practically unreasonable
if you're already using code (and similar fields) as a key all over
Moreover, I, for one, see no reason code could not be truncated to 32
characters, except that it might point to a problematic usage of
fields for both user input and application logic. But, hey, I'm not
even sure how I would fill out the code field, so don't listen to me
Is there a functional reason for fields to be used for both user input
and keying? Should a user be able to create two accounts, enter code
on both, and have them magically relate? I haven't seen any
functionality like this in my GnuCash journeys, so I doubt it, but
it's the only reason I can fathom to use code both as user input and
as a key.
More information about the gnucash-devel