Ian Smith-Heisters wrote: > On Wed, Nov 12, 2008 at 6:11 PM, Derek Atkins <[EMAIL PROTECTED]> wrote: > >> Hi, >> >> Quoting Eric Anopolsky <[EMAIL PROTECTED]>: >> >> >>> 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 _______________________________________________ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
