[GNC-dev] Data Model Schema

john jralls at ceridwen.us
Sat Aug 20 16:29:51 EDT 2022 

Scott,

No, accounting software isn't a fancy spreadsheet. The math part is a lot simpler, but accounting is all about rules. The rules part is harder. That's not to say that you can't use a spreadsheet to do your accounting, lots of people do, but a relational database approach is a much better fit.

There's no need to invent yet another financial data interchange format, there are plenty already: Intuit's proprietary QIF and QBO formats, the public OFX one (which Intuit calls QFX), and the banking industry's SWIFT and ISO-20022 are all widely used. 

You still haven't said what "enriched metadata" means or why we'd be interested in having it.

Regards,
John Ralls


> On Aug 20, 2022, at 12:06 PM, Scott Morgan <scott at adligo.com> wrote:
> 
> Hi John,
> 
>   Well, I did some work building an open source Java Accounting Model (adligo.org <http://adligo.org/>) a few times of the past two decades.  Now I find my self using QuickBooks online, so I have been looking for some collaborators and gnucash seems like the most successful project out there.   The enriched metadata would allow me to build ETL tools to go back and forth between QuickBooks, my system and gnucash. 
>   In my opinion accounting systems are basically the evolution of / fancy spreadsheets.  The main difference is the way transactions are linked in double-sided accounting.  Longer term I'm hoping for some sort of import-export standardization of accounting data.    The current standard seems to still be CSV files, which have problems binding the accounts together.  You can do this by tracking source systems and source Ids.  But it's still fairly painful.
>   So I figured I would start looking at the model you all were using inside of GnuCash.
> 
> On Sat, Aug 20, 2022 at 11:51 AM john <jralls at ceridwen.us <mailto:jralls at ceridwen.us>> wrote:
>> The SQL Schema is laid out in https://wiki.gnucash.org/wiki/SQL. There's an attached ERD, but note that it shows secondary keys and auxiliary tables that indicate how GnuCash treats the data once it's loaded. The actual schema defines only a few secondary keys and the data represented in the auxiliary tables is actually stored in the slots table. Also keep in mind that we're still using the both backends as an object store. 
>> 
>> What enriched metadata are you looking for? How will having it benefit GnuCash?
>> 
>> It seems rather presumptuous of you to unilaterally declare your intended derivative project a sibling.
>> 
>> Regards,
>> John Ralls
>> 
>> IOn Aug 17, 2022, at 9:06 AM, Scott Morgan <scott at adligo.com <mailto:scott at adligo.com>> wrote:
>>> 
>>> Hi All,
>>> 
>>>   Is the Data Model, / DB Schema stored somewhere with enriched metadata?
>>> If not I would like to volunteer to do this work, in part because I'm
>>> probably going to create a sibling Java project at some point to share the
>>> schema / data model.
>>> 
>>> Cheers,
>>> Scott
>>> 
>>> On Wed, Aug 17, 2022 at 11:00 AM <gnucash-devel-request at gnucash.org <mailto:gnucash-devel-request at gnucash.org>> wrote:
>>> 
>>>> Send gnucash-devel mailing list submissions to
>>>>        gnucash-devel at gnucash.org <mailto:gnucash-devel at gnucash.org>
>>>> 
>>>> To subscribe or unsubscribe via the World Wide Web, visit
>>>>        https://lists.gnucash.org/mailman/listinfo/gnucash-devel
>>>> or, via email, send a message with subject or body 'help' to
>>>>        gnucash-devel-request at gnucash.org <mailto:gnucash-devel-request at gnucash.org>
>>>> 
>>>> You can reach the person managing the list at
>>>>        gnucash-devel-owner at gnucash.org <mailto:gnucash-devel-owner at gnucash.org>
>>>> 
>>>> When replying, please edit your Subject line so it is more specific
>>>> than "Re: Contents of gnucash-devel digest..."
>>>> 
>>>> 
>>>> Today's Topics:
>>>> 
>>>>   1. Re:  Database abstraction (john)
>>>> 
>>>> 
>>>> ----------------------------------------------------------------------
>>>> 
>>>> Message: 1
>>>> Date: Tue, 16 Aug 2022 09:15:46 -0700
>>>> From: john <jralls at ceridwen.us <mailto:jralls at ceridwen.us>>
>>>> To: Thomas <tduellmann+gnucashdevel at gmail.com <mailto:tduellmann%2Bgnucashdevel at gmail.com>>
>>>> Cc: gnucash-devel at gnucash.org <mailto:gnucash-devel at gnucash.org>
>>>> Subject: Re: [GNC-dev] Database abstraction
>>>> Message-ID: <B25CDEDA-378C-42BF-A33B-CC8A8D1600BF at ceridwen.us <mailto:B25CDEDA-378C-42BF-A33B-CC8A8D1600BF at ceridwen.us>>
>>>> Content-Type: text/plain;       charset=us-ascii
>>>> 
>>>> 
>>>> 
>>>>> On Aug 16, 2022, at 4:54 AM, Thomas <tduellmann+gnucashdevel at gmail.com <mailto:tduellmann%2Bgnucashdevel at gmail.com>>
>>>> wrote:
>>>>> 
>>>>> Hello everybody,
>>>>> 
>>>>> for quite some time I am interested in doing some simple queries against
>>>> the data I have in my GnuCash books that I found hard to specify in custom
>>>> reports or the like.
>>>>> As I have some technical background, I was thinking whether there is
>>>> some DB abstraction that allows easier queries than the core database model
>>>> behind GnuCash. At least for me, it would be easier to write some Python
>>>> code or SQL queries rather than writing a custom GnuCash report tbh.
>>>>> 
>>>>> So a representation closer to the visual representation in GnuCash where
>>>> it is easier (without countless JOINs for example) to make queries (e.g.,
>>>> get all transactions in a specific account that are higher than a certain
>>>> amount).
>>>>> 
>>>>> I'd appreciate any kinds of hints on what you would use for such
>>>> purposes!
>>>>> 
>>>>> Thanks a lot in advance!
>>>> 
>>>> For that specific example you could use the Python bindings to call
>>>> xaccAccountGetSplitList() and filter the result by amount in a list
>>>> comprehension. The API docs at https://code.gnucash.org/docs/MAINT are a
>>>> good place to discover more functions like that.
>>>> 
>>>> Regards,
>>>> John Ralls
>>>> 
>>>> 
>>>> 
>>>> ------------------------------
>>>> 
>>>> Subject: Digest Footer
>>>> 
>>>> _______________________________________________
>>>> gnucash-devel mailing list
>>>> gnucash-devel at gnucash.org <mailto:gnucash-devel at gnucash.org>
>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-devel
>>>> 
>>>> 
>>>> ------------------------------
>>>> 
>>>> End of gnucash-devel Digest, Vol 233, Issue 6
>>>> *********************************************
>>>> 
>>> 
>>> 
>>> -- 
>>> Regards,
>>> Scott Morgan
>>> President & CEO
>>> Adligo Inc
>>> http://www.adligo.com <http://www.adligo.com/>
>>> https://www.linkedin.com/in/scott-morgan-21739415
>>> A+ Better Business Bureau Rating
>>> <https://www.bbb.org/chicago/business-reviews/computer-software-publishers-and-developers/adligo-inc-in-chicago-il-88381256>
>>> https://github.com/adligo
>>> 
>>> By Appointment Only:
>>> 1-866-968-1893 Ex 101
>>> scott at adligo.com <mailto:scott at adligo.com>
>>> skype:adligo1?call
>>> Send Me Files Securely:
>>> *https://www.sendthisfile.com/f.jsp?id=ewOnyeFQM18IDRf7MMIdolfI
>>> <https://www.sendthisfile.com/f.jsp?id=ewOnyeFQM18IDRf7MMIdolfI>*
>>> https://discord.com/
>>> Adligo#3066
>>> _______________________________________________
>>> gnucash-devel mailing list
>>> gnucash-devel at gnucash.org <mailto:gnucash-devel at gnucash.org>
>>> https://lists.gnucash.org/mailman/listinfo/gnucash-devel
>> 
> 
> 
> -- 
> Regards,
> Scott Morgan
> President & CEO
> Adligo Inc
> http://www.adligo.com <http://www.adligo.com/>
> https://www.linkedin.com/in/scott-morgan-21739415
> A+ Better Business Bureau Rating <https://www.bbb.org/chicago/business-reviews/computer-software-publishers-and-developers/adligo-inc-in-chicago-il-88381256>
> https://github.com/adligo
> 
> By Appointment Only: 
> 1-866-968-1893 Ex 101
> scott at adligo.com <mailto:scott at adligo.com>
> skype:adligo1?call
> Send Me Files Securely: 
> https://www.sendthisfile.com/f.jsp?id=ewOnyeFQM18IDRf7MMIdolfI
>  <>
> https://discord.com/
> Adligo#3066

More information about the gnucash-devel mailing list