5

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 1

Diagram 2

Diagram 2

Update:

A 3rd diagram based on Daniel's answer below

Diagram 3

Diagram 3

Nabegh
  • 153
  • 1
  • 1
  • 5

2 Answers2

6

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.
Daniel Hutmacher
  • 9,173
  • 1
  • 27
  • 52
0

Without more knowledge of the applciation domain, it is difficult to give a good answer.

I would however usually prefer design #2, as the "artifacts" (Income statement, Balance sheet and cash flow) (i suppose) always belong to one specific company / financial statement. With design #1, multiple financial statements could relate to the same artifacts, which (i suppose) would be odd.

I'm not sure I see the purpose of Financial Statement; couldn't the articfacts just relate directly to a company, and include a year? That way it is easy to query for specific types of statments in specific years, with one less join.

If the content of every artifact is just text, you could also consider storing them in the same table, with the text from statements items, and a type attribute; again saving a join. As i understand your statement items to be 1:1 with the artifacts.

I hope my thoughts are useful to you. Have a great day :-)

Michael Valentin
  • 1
  • 1