String lengths in the SQL backend

Mark Cochran remarkable at charter.net
Sat Nov 15 18:11:03 EST 2008


Ian Smith-Heisters wrote:
> 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
>
>   
I agree with Ian in the context of what I would call a "surrogate key", 
i.e., a system-generated value used in a relational database to maintain 
relational identity and integrity between related tables.  Such a 
surrogate key should be meaningless to anyone/anything but the 
database.  It does not replace, however, user-entered, user-identified 
values that uniquely identify a record in 
human-language/business-context sense.  As a dba and developer, I'll use 
user-entered values as keys when they are (unique, of course) unchanging 
- i.e., names (persons, or account names) are easily changed, and so are 
bad candidates for keys (apart from their non-uniqueness).  I use 
surrogate keys when it requires a combination of fields to make a unique 
value (a composite key), and the number of fields numbers more than 
three (an arbitrary number).  I do this mainly for ease in writing SQL 
statements that join tables containing the composite key - the 
single-field surrogate key can replace the composite key for that join 
purpose.  That surrogate key is still meaninless, though, and I still 
crate a unique constraint in the [parent] table containing the composite 
fields.
This is probably way more technical information that an average gnucash 
user wants, by the main reason i would like to see a rdbms backend is so 
that I could write SQL queries against it.

Mark Cochran


More information about the gnucash-user mailing list