String lengths in the SQL backend

[Ian Smith-Heisters]

On Wed, Nov 12, 2008 at 6:11 PM, Derek Atkins <warlord at mit.edu> wrote:
> Hi,
>
> 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.
>
>> Cheers,
>> Eric
>
> -derek
>

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
the place.

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.

-ISH