String lengths in the SQL backend

[Rolf Leggewie]

Hello everybody,

Herbert Thoma wrote:
>> What exactly is the use-case for using codes as a primary key?

I'm not following this list daily.  I just joined this discussion which
also concerns me to a great extent.

Phil already gave a bit of explanation.  I'll try to give my
perspective, too.

First, my motivation stems from the frustrations I have experienced over
the last 18 months or so trying to get gnucash into a shape to support
German business accounting.  I finally had to realize that almost all
components of gnucash are too inflexible (and of course my capability to
change that is also limited, but I knew that before).  Phil's SQL
backend now gives me the opportunity to evade the rigidities of gnucash
in areas where this is needed.  I can immediately access the data I
entered into gnucash and get results quickly.  SQL allows me to slice
and dice the data in any way I see fit (gnucash reporting is an absolute
nightmare).  While obviously most data will be supplied by gnucash,
there are some additional data repositories that need to be linked in.
One example is the relation from account to tax categories (no, the
stuff offered by gnucash is not sufficient, those following gnucash
closely will know I tried very hard).  I link in data by adding tables
to the MySQL database and relating that to gnucash data with the help of
keys.

> In my opinion the mapping of account to code is a one-to-one
> relationship and I see no reason for account code being used
> as a primary key, one could (and should) use the account key.

For the plain DATEV SKR04 that is true.  For the way that it is being
applied in gnucash, it is not.  I guess, this should already be clear by
the code field being available.  If there was a one-to-mapping
("eineindeutig"), one could just use the user entered data from the code
field and have that as the guid.  The randomly-generated GUID itself
would have become redundant data.

Just one real-live example for further clarification.  Gnucash offers
placeholder accounts.  The gnucash SKR04 makes liberal use of that to
present the user with a hierarchy of accounts.  The placeholder accounts
don't have any entry in the code field.  There is no 1:1 relation.  With
guid being a primary key, it naturally is a 1:n-relation, though, and
this should help.

That being said.  What I want is a relation between the code field of an
account (interpreted as DATEV code in the gnucash SKR04) and the tax
category (this will be outside of gnucash).  That lead me naturally to
use the code field as the foreign key.  While it will complicate things
a little bit, I may probably use the guid space as key which is
inherently larger than the code space.

Regards

Rolf