• 0

DB Structure for Storing Financial Statements


Question

I want to store company financial information in a MySQL database. The database will need to store information from companies' balance sheets, income statements, and statements of cash flow for multiple years. I need to store revenues/net income/etc for each year since 2000 for each company in the database. The only way I can think to make a table like this would be to make a column named 'revenue' and store the information in an array. But that way seems really messy; is there a better alternative (besides hard-coding '2011 Revenue,' '2010 Revenue,' etc?

8 answers to this question

Recommended Posts

  • 0

Er, how about this: I plan on building a gigantic table where '2011 Revenue' '2010 Revenue' ... '2011 Net Income' etc will be stored on one row for each company. Can anyone think of a better way to do this? (Would storing all of the information in arrays be better?).

  • 0

You're not giving us much information on the details, so it will be difficult to give you a schema. Basically you will want to look into http://en.wikipedia.org/wiki/Database_normalization Also I would HIGHLY recommend encrypting some or all of this data.

Good luck.

  • 0

It also depends on how much information you have.

If you were only going to have one record for revenue for each year, you'd want something like

yearkey - revenue

which you could then query to get a revenue for a year (or multiple years)

having columns:

revenue_2011 revenue_2012 etc

is almost always a bad idea, although it depends again on what the makeup of the data is. If you have millions of companies revenues, and you were more interested in querying (or comparing) revenues from given years against companies, this might be a better structure for you - but I doubt it even if that scenario.

  • 0
  On 29/12/2012 at 16:57, DomZ said:

having columns:

revenue_2011 revenue_2012 etc

is almost always a bad idea,

THIS!!!!! Do not have columns for each date,month or year. Please PLEASE do not do this. (WOW have I had to go into so many jobs that use this sort of model!) If there are to be multiple dates, then a separate table is needed.

  • 0
  On 29/12/2012 at 16:18, James Rose said:

You're not giving us much information on the details, so it will be difficult to give you a schema.

Go here and ctrl + f "ITEM 8. FINANCIAL STATEMENTS AND SUPPLEMENTARY DATA"

I'm basically going to take the information from the financial statements and put it in a database. I'm going to do it for a few hundred companies and will make the database searchable based on the financials (e.g., average free cash flow margin > 10%).

  On 29/12/2012 at 18:05, James Rose said:

THIS!!!!! Do not have columns for each date,month or year. Please PLEASE do not do this. (WOW have I had to go into so many jobs that use this sort of model!) If there are to be multiple dates, then a separate table is needed.

This is what I was trying to avoid. How do I get around it?

  • 0

It depends on how robust and maintainable you want your database to be. Just looking at the website you linked to, I've come up with the following model, assuming you want data on a yearly basis, written in pseudo-SQL:

--The below table contains a row for each line item in the statement - "Operating expenses", "Net Income", etc.

LineItems (

LineItemID int primary key identity,

LineItemName varchar(100),

IsExpense bool <- This is so you can easily filter between expenses and income - false is income, true is expense. You could also flip this.

)

--This stores info about the company.

Companies (

CompanyID int primary key identity,

CompanyName varchar(50)

)

--This is your primary table, the table where you will store the financial info. It links to the above tables.

Finances(

FinanceID int primary key identity,

CompanyID int,

Year smallint, --assuming you're aggregating by year - you should only have one line per line item per company per unit of time

LineItemID int,

Amount currency

)

This way, the database will remain compact, as you won't be storing a bunch of characters for every line item in the financial table, and it will be maintainable. I'd recommend indexing all foreign keys (the IDs that refer to other tables) if you database grows beyond thousands of rows, as performance will begin to suffer. You can construct a view to join the tables together into one giant table if you want.

This model will also have the benefit of being able to extend to an infinite number of years, should you wish to keep doing this. I'm used to MS SQL, but check to see if MySQL has a PIVOT function where you can pivot the "Year" rows into columns - that way you can create a view or return results to your client(s) identical to how it appears on the site.

  • 0
  On 11/01/2013 at 00:20, NateB1 said:

It depends on how robust and maintainable you want your database to be. Just looking at the website you linked to, I've come up with the following model, assuming you want data on a yearly basis, written in pseudo-SQL:

--The below table contains a row for each line item in the statement - "Operating expenses", "Net Income", etc.

LineItems (

LineItemID int primary key identity,

LineItemName varchar(100),

IsExpense bool <- This is so you can easily filter between expenses and income - false is income, true is expense. You could also flip this.

)

--This stores info about the company.

Companies (

CompanyID int primary key identity,

CompanyName varchar(50)

)

--This is your primary table, the table where you will store the financial info. It links to the above tables.

Finances(

FinanceID int primary key identity,

CompanyID int,

Year smallint, --assuming you're aggregating by year - you should only have one line per line item per company per unit of time

LineItemID int,

Amount currency

)

This way, the database will remain compact, as you won't be storing a bunch of characters for every line item in the financial table, and it will be maintainable. I'd recommend indexing all foreign keys (the IDs that refer to other tables) if you database grows beyond thousands of rows, as performance will begin to suffer. You can construct a view to join the tables together into one giant table if you want.

This model will also have the benefit of being able to extend to an infinite number of years, should you wish to keep doing this. I'm used to MS SQL, but check to see if MySQL has a PIVOT function where you can pivot the "Year" rows into columns - that way you can create a view or return results to your client(s) identical to how it appears on the site.

Ah, great! Thanks, this is helpful.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Patch My PC - Home Updater 5.2.3.0 by Razvan Serea Patch My PC Free is a reliable tool which can quickly check your PC for outdated software. The supported third-party programs include a large number of widely-used applications, including Adobe Reader, Mozilla Firefox, Java, 7-Zip, BleachBit, Google Chrome and many more. Patch My PC Home updater features: Updates over 500 common apps check including portable apps Ability to cache updates for use on multiple machines No bloatware during installations Applications install/update silently by default no install wizard needed Optionally, disable silent install to perform a manual custom install Easy to use user interface Change updated and outdated apps color for color blindness Option to automatically kill programs before updating it Create a baseline of applications if installing on new PC’s Quickly uninstall multiple programs Scan time is usually less than 1 second Set updates to happen on a schedule Skip updates for any application you don’t want to update Suppresses restarts when performing application updates Patch My PC - Home Updater 5.2.3.0 changelog: Startup Manager New tab to manage which apps launch at startup. This helps speed up your boot time and gives you control over what runs in the background. Generate Diagnostic ZIP You can now create a diagnostic ZIP file from the About page. This helps if you need to send logs on our support forum for Home Updater. Remove Portable Apps Right-click any portable app in the App Catalog or Uninstaller page to remove it directly. Applications Added FFmpeg (Full Shared) – Portable Fing G-Helper – Portable IntelliJ IDEA Community Edition K-Lite Basic Codec Pack K-Lite Full Codec Pack K-Lite Standard Codec Pack KeePass Password Safe v1 LibreOffice Help Pack MemTest86 – Portable Nexus Vortex Nvidia Profile Inspector – Portable Pale Moon – Portable ViVeTool – Portable WinCDEmu Windows PC Health Check Wise Video Converter Applications Removed Driver Easy Download: Patch My PC 5.2.3.0 | 54.8 MB (Freeware) Download: Patch My PC Portable | 31.0 MB (Portable) View: Patch My PC Free Homepage | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • "For starters, Microsoft Edge is getting a media control center. This feature is intended to let you control multiple media sources from any website in a single place." Oh, I've got this Media Control and couldn't find how to disable it. I hate it when a button appears on a toolbar where there was none just before I press Play. I probably would find it at least somewhat useful if I could start playing media from any opened tab, but now it only shows controls for media I've already started playing. If anyone knows how to disable it - I'd appreciate a hint.
    • Now that he turned on Trump and both sides hate him does anyone want this stupid thing?
    • This is what I thought of earlier today because it seems a bit stupid to have an iPhone 17 running iOS 26 (or iOS 2026 / or even iOS 25/2025). Just make it simple so that the year of the hardware release and the software release are in sync. I personally think they should go with 25 or 2025 (not 26 or 2026), but syncing the hardware and software version numbers could be easier to keep track of. At first, it will maybe be jarring due to all of the changes across the ecosystem, but from that point on it will be easier to keep track of.
    • my dad is experiencing the same thing except it's with Excel. the font became thin compared to windows 10, all the settings the same. i've chalked it up to it being that its connected via DVI instead of HDMI. is your setup the same? i have no technical reasons to believe it's DVI, just a plain guess since the other screen he's connected to seems better to me although may just be my mind playing tricks.  also, why don't you change the text size in accessibility? maybe this will help?   
  • Recent Achievements

    • Week One Done
      abortretryfail earned a badge
      Week One Done
    • First Post
      Mr bot earned a badge
      First Post
    • First Post
      Bkl211 earned a badge
      First Post
    • One Year In
      Mido gaber earned a badge
      One Year In
    • One Year In
      Vladimir Migunov earned a badge
      One Year In
  • Popular Contributors

    1. 1
      +primortal
      492
    2. 2
      +FloatingFatMan
      256
    3. 3
      snowy owl
      248
    4. 4
      ATLien_0
      224
    5. 5
      +Edouard
      189
  • Tell a friend

    Love Neowin? Tell a friend!