Database Design – Financial Database Design

database-designdatabase-diagrams

I am working on an application to store financial data and need to model the tables where the data for several financial statements will be stored. I came up with the following two designs but I can't decide on which one to use. Appreciate your input.

Diagram 1

Diagram 2

Update:

A 3rd diagram based on Daniel's answer below

Diagram 3

Best Answer

IMO, the balance sheet, income statement and cash flow tables can be the same. Here's my take on it:

-- Companies
CREATE TABLE company (
    [id]      int NOT NULL PRIMARY KEY,
    [name]    varchar(255) NOT NULL
);

-- "Balance sheet", "IFRS Income statement", etc
CREATE TABLE statement (
    [id]      int NOT NULL PRIMARY KEY,
    [name]    varchar(255) NOT NULL
);

--- "Tangible assets", "Outstanding stock", etc
CREATE TABLE statementRow (
    [id]      int NOT NULL PRIMARY KEY,
    statementId int NOT NULL,
    rowOrder  int NOT NULL,
    rowTitle  varchar(255) NOT NULL,
    rowDescription varchar(max) NULL,
    rowProperties varchar(max) NULL,
    FOREIGN KEY (statementId) REFERENCES statement ([id])
);

--- The facts
CREATE TABLE statementFact (
    companyId      int NOT NULL,
    statementRowId int NOT NULL,
    [date]         date NOT NULL,
    amount         numeric NULL,
    PRIMARY KEY ([date], statementRow),
    FOREIGN KEY (companyId) REFERENCES company ([id]),
    FOREIGN KEY (statementRowId) REFERENCES statementRow ([id])
);

Advantages of this model:

You can have different types of balance sheets, income statements, etc, in order to cover future reporting needs
The model defines the ordering of the rows for each statement (don't rely in an identity column for that, because you won't be able to insert rows later on)
Using "date" instead of "year" allows you to publish statements more than once per year, i.e. per quarter or month, or even ad-hoc.
The rowProperties field allows you to add information like if the row should be boldface, italics or other formatting properties.
Optionally, you may want to move "companyId" from statementFact to "statement" if certain reports only apply to certain companies.

Related Solutions

Database Design for updatable sequential records

Your design seems reasonable to me. While you do have to update all subsequent records when new processes are added or deleted that is easy to accomplish. You just issue an update like:

UPDATE ProcessOrder
SET ProcessOrder = ProcessOrder+1
WHERE ProcessOrder >= [step# where you want to insert]

and then do your insert or delete.

The only other way I can think of would be to design the schema to store the next process id on the row. Something like:

ProcessID | ParentProcessID | NextId
--------------------------------------------------
UUID2     | UUID1           | UUID3
UUID3     | UUID1           | UUID4
UUID4     | UUID1           | NULL

Then if you insert a new step - say between UUID3 and UUID4, you perform more of a linked list operation which will update UUID3|UUID1's NextId to UUID5 and then just insert the new UUID5 with a NextId of UUID4.

This will reduce the UPDATEs to 1 in most cases, but it will make querying the process more difficult as now you have to walk the list from top to bottom to list out step by step.

You need to decide which process you want to favor - inserting and updating or retrieving. If you favor retrieval (which you might if changes are infrequent and reporting is frequent, and the lists are short), then go with your original design. If you favor insert and update (which you might if changes are happening all the time and reporting is infrequent, or lists are really really long), then go with the linked list approach.

I hope this helps. Interested in what other solutions the community might come up with as I'd love to broaden my knowledge around this!

Database Design – Double Entry Bookkeeping

Indeed, the single row accounting schema proposed allows to do proper double entry accounting (to always specify the account debited and credited) without introducing the redundancy of the "amount" data.

The one row schema gives you an implementation of a double entry that balances by construction, so that it is impossible to ever "loose balance". The machine may recompute the ledgers on the fly.

You have to do 2 select instead of one to retrieve a ledger.

Note, besides transaction split, there are other transactions such as foreign exchange might end up with 2 records instead of 4. It all depends if you would denormalize a bit just enter 4 transactions with similar description.

You may prevent the entry or modification of any transaction to maintain an audit trail, this is required if you want to be able to audit the transaction log.

It appears that in the thread above, for the CPA, "fully normalized" appears to mean rules recognized by all accountants, whereas for the programmer it has a diferent meaning, that there is no derived or redundant data stored.

All there is to accounting data is a set of transactions which give amount and the accounts from which and to which they flow, along with their date, some description (and other attachements). Ledgers and balances are simple views derived from this transactional data by doing sums.