Postgresql – Database Normal Form (financial statement)

database-designnormalizationpostgresql

I would like some advice on how to design the database for financial statement data which objectively addresses the tradeoffs between usability, performance, maintenance and adherence to normal form. Also, what about my proposed design(s) violate normal form?

Database (in NF3?)

TICKER TABLE

ticker |
wmt
...

YEAR TABLE

year |
2014
...

QUARTER TABLE

quarter |
q1
q2
q3
q4

YEAR_QUARTER TABLE

year    | quarter    |
year_fk | quarter_fk
2014_fk | q1_fk
...

TICKER_QUARTER TABLE (necessary because fiscal/calendar dates are not the same for all companies)

ticker    | quarter         | date
ticker_fk | year_quarter_fk | e.g. 2014-11-11
...

ITEM TABLE

item    |
revenue
cogs
...

Final Table Options

TABLE 1

ticker    | date                 | item       | value
ticker_fk | ticker_quarter_fk    | item_fk    | 100.00
wmt_fk    | ticker_quarter_fk    | cogs_fk    |  65.00
...

QUESTION: Does this Table 1 option properly adhere to normal form?

TABLE 2

ticker | date              | revenue | cogs  | ...
wmt_fk | ticker_quarter_fk | 100.00  | 65.00 | ...
...

I don't like this option because if I add a new item I need to append a new column.

Or

I scrap the item_fk reference table and have a table for EACH AND EVERY item, e.g.

TABLE REVENUE

ticker | date              | value
wmt_fk | ticker_quarter_fk | 100.00

TABLE 1…N (cogs, sg&a, …N)

ticker | date              | value
wmt_fk | ticker_quarter_fk | n

I don't like this option because I have to create a new table for each item and I often have use for multiple items in a single query so this option would require a lot of joins.

Best Answer

My team once had to build a database for reporting financials with alternative time windows, including fiscal years, fiscal quarters, and fiscal months. The relationship between dates and these units was documented, but really messy. So here is what we did.

We created a table, call it Almanac, with one row per date. (In reality, we had one row per work shift, but I'm leaving out that complexity for the sake of clarity). We wrote an Almanac generating program that applied the strange rules for fiscal years, quarters and months to each date over a ten year period. The PK for the Almanac table was the Date, although we couldn't use the name Date because it was a keyword. The other columns were items like the fiscal year, the fiscal quarter, and flags like "Company Holiday". Notice that this program had one output, but no inputs.

Once the almanac was built, joining any other table with the almanac over a date field made it trivial to slice and dice by fiscal time periods, regardless of how obscure the rules were. This worked like a charm.

(If you must know, the Almanac generator was written in COBOL+SQL. This was a long, long, time ago, when Oracle was very young.)

Related Question