We are talking about different things. The referennce ID is an ID assigned by the originator or the transaction, say an invoice number from a vendor or a check number from a bank whereas the cross reference is assigned by the application software and represents a key in an index on the table.
The points made by another poster are significant - not only is the design of an accounting database important from a technical perspective, it must also not violate the Doctrines of Accounting(consistency, disclosure etc). Since transactions are a matter of legal record they cannot be changed or deleted, only added, and it should be possible mto prove that they cannot be changed. The locally assigned cross reference number tagging the transaction set can also be used as an audit tool to prove the absence of deletions. BareFeet wrote: > Hi John, > >> You still miss the point of the cross reference ID. It is NOT the >> reference ID od the document, such as an invoice or check number, >> but it >> more like the row ID used by Sqlire as a unique key for a DB row. > > I thought, from your explanation, that was what you were using the > "Reference ID" for, as you said: > >>> reference ID to tag the particular complete transaction > > Anyway, it doesn't matter since I think we're talking about the same > thing, but with different column names. If you want to clarify, it > would help if you posted a schema of the three tables and the > relationships between them. > >> You could normalize out the date and have stored as associated with >> the cross reference. The cost of that would be an extra row lookup >> each time you want to get the date of an accounting transaction. A >> performance and code complexity decision. > > It's also an SQL design issue which impacts performance and the > ability to extract different reports. De-normalized databases are > fundamentally a bad thing. You wouldn't, for instance, design a > meeting database where every meeting event explicitly store the name > and contact details of the person you were meeting. You'd store it in > a People table and refer each event to it. Linking the data through > "an extra row lookup" is trivial and optimized within the SQL engine. > > Thanks, > Tom > BareFeet > > -- > Comparison of SQLite GUI tools: > http://www.tandb.com.au/sqlite/compare/?ml > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
