New data field for invoice object

John Ralls jralls at
Sat Oct 8 15:49:22 EDT 2011

On Oct 8, 2011, at 12:34 PM, Christian Stimming wrote:

> Am Samstag, 8. Oktober 2011 schrieb John Ralls:
>> On Oct 8, 2011, at 9:23 AM, Geert Janssens wrote:
>>> I'm working on introducing full credit note support in GnuCash [1]. After
>>> thinking it through for a long time I came to the conclusion I will need
>>> to add an extra parameter to the invoice object that has to be saved and
>>> restored from the data file.
>>> I could use a new kvp, (...)
>>> And since kvp's are not nice for the sql backend, I think it will be
>>> better to use a true field.
>> The way you'd implement an optional type field in an RDB is to create a new
>> table for it with two fields, a key (which would be the invoice GUID) and
>> the type, and the code for handling that table would have to examine the
>> database version and not use it if it's an older version. Similarly, your
>> new credit note code would have to check with the backend to see if it's
>> OK to create the note, because an older version of Gnucash will do the
>> wrong thing with the data. This is a Bad Idea. We're tying ourselves in
>> knots worshipping backward compatibility. The current data model is not a
>> good design, but we can't fix it if we have to stay bidirectionally
>> compatible with older versions.
>> I don't remember offhand what distinguishes a bill from an invoice, but I
>> think it's which set of (identical) fields in the entry table are filled,
>> which is a poor design. I suggest that in the interest of better
>> normalization you go ahead and create the type field and consider
>> collapsing into a single table the invoice and entry tables -- or at least
>> shrink the entry table to a single set of fields (instead of the current,
>> identical, i_ and b_ sets) -- the purpose of which is determined by the
>> type field.
> I fully agree with the rant and the bad data model design.
> However, in cases like Geert brought up the decision is somewhat differently. 
> Geert needs a new data field for a new feature (that some real or hypothetical 
> customer is asking for). The rest of gnucash's features are working fine for 
> that customer. The question is now how to add this new field in the best 
> possible data modeling way while still only using the minimal effort required 
> on the coding side.
> If I understand John's reply correctly, he is saying that even though the kvp 
> design is very bad, any other potential optional field in the sql backend is 
> just as bad and broken in terms of data model. If this is correct, I'd say 
> Geert should take a deep breath and implement his needed data field as yet 
> another kvp. In xml, the writing and reading is done automatically, and in 
> SQL, it's rather ugly but is still implemented by some few calls to ensure the 
> writing of those kvp entries in the kvp table.
> The data model clean-up is then scheduled for a different time. Surely not a 
> good solution for now, but on the other hand IMHO the only realistic solution 
> for a usable software, given our current development capacity.
>> While I'm ranting, another poor design choice that jumps out is addresses.
>> The same fields exist twice in the customer table, once in the vendor
>> table, and once in the employee table. There should be a single address
>> table, and the other tables should have a foreign key to its primary.
> Completely agreed. However, also scheduled not for today.

Yes, there's no advantage to creating the separate table over using KVP.

Nope, sticking it in KVP is bad, too, because without the type field, an older version of Gnucash will see the credit note data and think that it's a regular invoice and really screw things up.

I think that we must accept that we can't add the feature in a way that's safe for older versions, add the field to the Invoice table, and increase the version level on the database (and the XML file) which will prevent older versions of 2.4 from loading it. 

John Ralls

More information about the gnucash-devel mailing list