String lengths in the SQL backend
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:
>> 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
> 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.
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
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.
More information about the gnucash-devel