SQLiteを使った会計システムの構築方法

逆引きXeroでSQLを教える

Scratch you own itch.

これは、プログラミングの学習方法を聞かれたときに、私がいつもしているアドバイスです。 Youtube のチュートリアルを見たり、プログラミングの本を読んだり、Reddit の投稿からコードをコピーしたりしても、プログラミングを学び始めたばかりでは何も得られません。

この投稿では、SQLite を使用して粗い会計データベースを構築する方法を紹介します。 公開データをコピーして SQLite に押し込み、そこから練習すればよいのではないでしょうか。

その理由は、会計データベースの作成は、クエリーから結合、ビュー、CTE まで、データベースと SQL のすべての側面をカバーするのに十分高度であるからです。 結局のところ、プログラミングは問題を解決するためのツールなのです。

SQLを使った会計システムを作るきっかけは、Xeroがどのように動作するかを観察したことでした。 Xeroはニュージーランド発祥のクラウド会計ソフトです。

Xero の良いところは、きれいなインターフェイスと、機能を拡張するために多くのアプリケーションを選択できることです。 確かに、ここで紹介する SQL は、システムが拡張する必要があるため、Xero が使用する正確な SQL デザインではありません。 しかし、これは非常に興味深いプロジェクトなので、ぜひやりましょう！

Accounting 101

あまり興奮する前に、まず会計について座学で学んでおきましょう。

会計の基本方程式は

Assets = Liabilities + Equity

この方程式は基本的に3つの部分

• 資産は企業のすべての資源
• 負債は企業が負うもの
• そして資本はオーナーのすべての投資、引き出し、利益または損失が蓄積されているものである
• このように、資産、負債、資本は、企業のすべての資産を意味し、また、負債、負債、資本は、企業が所有するものである。

右辺は、負債または資本によって、資産がどのように資金調達されたかを示しています。

上の式を展開すると、「資本」

Assets = Liabilities + Beginning Equity + Revenues – Expenses

Assets, Liabilities, Equity, Revenues, Expenses という 5 つの勘定は、会計システムで通常目にする勘定タイプに該当します。 この 2 つについて詳しく説明することもできますが、この記事では、すべての取引において

Debit = Credit

この 2 つの方程式が、会計サイクル全体で起こっていることを一般的に支配していることを理解していただく必要があります。

会計のバックグラウンドを持つ私にとっては、これはSQLを学ぶのに最適なプロジェクトだと思います。 結局のところ、プログラミングは問題を解決するためのツールなのです。 したがって、SQL を完全に学ぶには、難しい問題を「解決」したほうがいいかもしれません。

Xero の会計の実装

重要なことは、Xero は（会計士ではなく）ビジネスオーナーにとって日々の業務に役立つような方法で設計されている、ということです。

そのため、取引サイクルと内部統制を中心に設計されています。

Transaction Cycles

基本的な取引サイクルは以下のとおりです。

• 売上サイクル
• 購買サイクル
• 現金サイクル

Xeroでは、これらのサイクルを以下のように実行します。

Sales Cycle
Xeroに売上はInvoiceを使用して入力されます。 事業者が売上（現金売上または掛売り）に対して実際の紙の請求書を発行することを想像してください。

請求書はソフトウェアから直接印刷することができ、自動的に番号が付けられます。

裏側では、請求書は売上勘定と売掛金勘定を増やします。 ここでも、購入（現金購入または掛売り）に対して実際の請求書を発行するビジネスを想像してみてください。 これは、水道光熱費や棚卸資産などの通常のケースです。

請求書はソフトウェアから直接印刷することができ、ビジネスで行われる承認手続きを補足するために使用できます。

その下で、請求書は購買勘定と支払勘定（AP）を増加させます。 4つのタイプがあります。

• Invoice Payments – 未払いInvoiceの支払い
• Bill Payments – 未払いBillsの支払い
• Received Money – Invoice Paymentではない現金受領額です。 これは現金販売を含むかもしれませんが、インボイスを発行するつもりなら、インボイス機能を使用してください。
• 支出金 – 請求書の支払いではない現金支出です。

以上、取引サイクルについて説明しました。

内部統制

内部統制のためには、システム勘定の概念を理解する必要があります。 しかし、今回の目的では、以下のシステムアカウントのみを取り上げます

• 売掛金
• 買掛金
• 銀行勘定（銀行フィードにリンク）

これらの勘定は手動ジャーナルで使用することはできません。 つまり、Xeroは、Invoices、Bills、およびCash Transactions (Invoice Payments、Bill Payments、Received Money、Spent Money)を使用して、これらのアカウントの残高をサポートしてほしいということです。 これは、内部統制の Xero による実装です。

もちろん、非システム版の AR、AP、および銀行勘定を口座表 (COA) で作成することによって使用することができます。 しかし、それらに AR、AP、および銀行の勘定科目を使用することはできません。

覚えておくべき重要なことは、Xero は（会計士ではなく）ビジネス所有者がビジネスの日常業務に役立つように設計されていることです。

Caveat: 私たちはウサギ小屋には行かない

会計を設計することは本当に複雑なのです。 このトピックをカバーするだけでも、複数のブログ記事が必要になるでしょう。 したがって、簡単にするために、次のような前提条件を作成することにします (Xero がこれらをどのように実装しているかということではありません)

• Invoice and Bill Payments
  Invoice Payments は、2 つ以上の請求書を一括して支払うことが可能です。 つまり、部分的な支払いは許可されません。
• Inventory
  ここでは、インベントリ項目を使用するつもりはありません。 販売または購入の場合、在庫アカウントにマッピングされた在庫アイテムを作成するのではなく、在庫アカウントを直接使用することにします。

仮定は以上です。 データベースを設計した後、読者はこれらの仮定を解除し、可能な限り Xero を模倣することができます。

SQL の基本

さて、Xero のデータベース構造を再構築する前に、データベースについて簡単に説明しましょう。 各テーブルは、レコードと呼ばれるデータの行で構成されます。

データベースを操作するプログラムはデータベース管理システムまたはDBMSと呼ばれます。 簡単な例として、DBMSはExcelのプログラム、データベースはExcelのワークブック、テーブルはExcelのワークシートです。

データベースとExcelワークブックには2つの大きな違いがあります。

データの表示はデータの保存と別です。

つまり、データベースのデータを直接越えて、編集することができないのです。 (他の DBMS プログラムには、データベース内のデータに直接アクセスして、スプレッドシートのように編集することができる GUI があります。

テーブルは通常、関係を形成するために互いにリンクされます。

関係には、1 対 1、1 対多、または多対多があります。

1対1の関係は、「テーブル行が別のテーブルの 1 行のみに関係し、逆も同様」であることを意味します。 例としては、従業員名と納税者番号です。

この種のデータは、2 つのテーブルに分離する利点がないため、通常、1 つのテーブルに含まれます。 例として、Invoices to InvoiceLines があります。

そして、あなたが推測しているように、多対多とは「テーブル行が他のテーブルの1つ以上の行にのみ関連し、逆もまた同様である」ことを意味します。

1 つの請求書が異なる支払トランザクションによって部分的に支払われることがあり、1 つの支払いが異なる請求書に部分的に支払われることがあります。

データベースはどのようにしてこれらの関係を知るのでしょうか。 主キーと外部キーの使用によるものです。

主キーは、ある行を別の行から区別するために必要です。

一方、外部キーは、別のテーブルからの主キーです。

1対多の場合、「1」側が主キーを含み、「多」側がその主キーを外部キーとして含む。 上記の例では、ある請求書に属するすべての行を取得するために、外部キーが特定の請求書番号に等しいInvoiceLinesテーブルに問い合わせます。

多対多の場合、「結合」テーブルと呼ばれる第3のテーブルを使用して、関係を2つの1対多関係に分割します。 たとえば、部分支払システムでは、Invoicesテーブル、Paymentsテーブル、InvoicePaymentsテーブルを結合テーブルとして使用します。 InvoicePaymentsテーブルの主キーは、InvoicesテーブルとPaymentsテーブルの主キーを合成した複合キーで、以下のようになります

。

結合テーブルには、InvoicesテーブルとPaymentsテーブルの結合以外の目的がないため、他のデータは含まれていないことに注意してください。

ある支払取引で支払われた請求書、たとえばPAY 1を取得するために、結合テーブルを使ってInvoiceテーブルとPaymentsテーブルを結合し、payment_id = "PAY 1"を問い合わせます。

簡単に例えると、DBMS は Excel プログラム、データベースは Excel ワークブック、テーブルは Excel ワークシートです。

Xero の実装を設計する

さて、Xero についての基礎知識を得て、そのデータベース構造のラフスケッチを作成し始めましょう。 ここでは Table_Name 形式を使用することに注意してください。 これは、大文字の単語をアンダースコアで区切ったものです。 また、テーブル名には複数形の名前を使うことにします。

Sales Cycleの場合。

• Invoices
• Customers – 顧客は多くの請求書を持つことができますが、請求書は多くの顧客に属することはできません
• Invoice_Payments – Invoice_Paymentsと請求書はそれぞれ1対多（部分支払なし）という今の仮定を思い出してください
• Invoice_Lines – 請求書とCOAをつなぐテーブルです。 1つの勘定科目が複数の請求書に表示されることがあり、1つの請求書が複数の勘定科目を持つことがあります。
• Chart of Accounts (COA)

Purchases Cycleの場合。

• Bills
• Suppliers – サプライヤーは多くの請求書を持つことができますが、1つの請求書が多くのサプライヤーに属することはできません
• Bill_Payments – 今のところ、Bill_PaymentsとBillsはそれぞれ一対多関係という仮定を思い出してください
• Bill_Lines – BillsとCOA間の結合表です。 4245>
• COA – 販売サイクルの上記と同じです。 念のため、ここに書いておきます。

キャッシュ・サイクルの場合。

• Received_Moneys – オプションでCustomer
• Received_Money_Lines – これは結合テーブルです。 Received_MoneysとCOA
• Spent_Moneys – Supplier
• Spent_Money_Lines – Spent_MoneysとCOA

間の結合テーブルである。 この図は、データベース用語でEntity-Relationship DiagramまたはERDと呼ばれます。 1 対多の関係は 1 – M で、多対多の関係は M – M で指定します。

結合テーブルは、多対多の関係を持つテーブルでは暗黙的に存在するため、上の図では示されていません。

主キーは id というフィールド/列名を持ち、外部キーは table_id という形式になります。table は「多」側のテーブルの名前で、単数形です。 例えば、Invoicesテーブルの場合、外部キーはSQLコードではcustomer_id.

Timeとなります。 ここにあります。

A couple of things here:

• SQL コマンドは大文字小文字を区別しません、CREATE TABLE は create table
• The IF EXISTS and IF NOT EXISTS are optional.SQLコマンドは大文字小文字を区別しません。 SQLコマンドのエラーを防ぐために使っているだけです。 例えば、存在しないテーブルをドロップすると、SQLiteはエラーを出します。 また、既存のテーブルを誤って上書きしないように、テーブルの作成コマンドに IF NOT EXISTS を付けました。
• DROP TABLE コマンドには注意してください! 既存のテーブルにコンテンツがあっても警告なしに削除してしまいます。
• また、テーブル名は全角で書いても書かなくてもかまいません。 テーブル名にスペースがある場合は、バックスティック(`)で囲む必要があります。 大文字と小文字は区別されません。 SELECT * FROM Customers は select * from customers.

と同じです。

SQL は構文に関して少し緩いですが、SQL コードで一貫性を保つように努力すべきです。 外部キーが多数側にあることも覚えておいてください。

いくつかのテーブルは外部キーのために別のテーブルへの依存として機能するため、順序は重要です。 たとえば、前者は後者の依存関係であるとして、あなたは最初のInvoice_PaymentsをInvoiceテーブルの前に最初に作成する必要があります。 ここでのトリックは、ERD の端から始めることで、それらは外部キーの数が最も少ないものです。

また、このリンクでコンテンツのない SQLite のサンプル データベースをダウンロードできます。 ダウンロードはこちらから！

Adding Contents to Our Database

さて、サンプルデータベースが手に入ったので、データを入力してみましょう。 サンプルデータはこちらからダウンロードできます。必要に応じてCSVに分解してください。

クレジットがプラス、クレジットがマイナスで表示されることに注意してください。

この記事では、DB Browserのインポート機能を使って、上記のExcelファイルからCSVをインポートするだけにしています。 例えば、Customers.csvをインポートするには

• Customersテーブル

• File > Import > Table from CSV file でCustomersを選択し、Customers.csvを選択し、CSVテーブルからCustomers.csvをインポートします。csv

• Check Ok/Yes to all successive prompts to import the data.で、データをインポートしてください。

次のSQLコマンドを発行すると、データベース内のすべてのCustomersが表示されます。

最初のステップは、請求書、請求書、Received_Moneys、およびSpent_Moneysトランザクション用のトランザクションビューを作成することです。 コードは次のようになります。

最初の2つのステートメントでは、CTE（キーワードrecursiveを使用）を使用しています。 CTEは、請求書取引とそれに対応する支払いについて1つのビューを取得するために、4つのテーブルを結合するのに便利です。

上記のコマンドを実行した後、データベースは次の4つのビューを持っているはずです。

最後に、Trial BalanceまたはTB用のコードを作成しています。 TB は、データベースを設計したときに定めたルールに従った、トランザクションの残高のコレクションであることに注意してください。 たとえば、最初のクエリは、Invoice トランザクション (主に Sales) のすべてのクレジットを取得しようとします。

これを実行すると、次のような TB が得られます。

これを Excel にして、借方が貸方に一致するかチェックできます（私はこれをやりました）。 借方と貸方の合計はそれぞれ14115と-14115です。

Hooray! You’ve made It

会計システムの作成は本当に複雑です。 私たちは、基本的に、コンセプトからERD、作成、クエリまで、データベース設計の全領域を探りました。 7219>

私たちは概念にもっと焦点を当てるために、意図的にデータベースを制限していることに注意してください。 これを解除して、制限のない別のデータベースを構築することができます。

以上です。 これであなたも SQL ニンジャです! おめでとうございます！

Leanpub でまもなく出版される私の 2冊目の書籍「会計データベース設計」もチェックしてください！(Copyright: A.S.C.)