• 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

    • Last chance to download DearMob iPhone Manager for PC/Mac (worth $79.95) for free by Steven Parker Claim your complimentary copy worth $79.95 for free today, before the offer ends today on June.3. Download a free licensed copy of DearMob iPhone Manager for effortless backup, restore, and flexible data transfer. Manage videos, photos, music, apps, contacts, ebooks, and 17+ other file types without data erasing risks. Easily transfer, manage, and back up your iPhone or iPad data--safely and without data loss. Free up space and clean unwanted files in just a few clicks. Get your free licensed copy for PC or Mac, fully compatible with all iOS and iPadOS devices. Highlights: Lossless Photo Transfer: Move or albums with zero quality loss; convert HEIC to JPG for easy sharing. Batch Video Export: Transfer videos to your PC without compression, freeing up valuable iPhone space. Custom Music & Ringtones: Add music, playlists, or create ringtones from your favorite songs. One-Click Backup & Restore: Protect your data with fast, reliable backups and restores, plus password encryption Comprehensive Data Management: Manage photos, music, videos, messages, apps, contacts, ebooks, and more. Offline Management: Take full control of your data without needing an internet connection. This free offer ends on 6/3/2025—don’t miss out! How to get it Please ensure you read the terms and conditions to claim this offer. Complete and verifiable information is required in order to receive this free offer. If you have previously made use of these free offers, you will not need to re-register. While supplies last! Free download: DearMob iPhone Manager for PC/Mac (worth $79.95) Offered by Digiarty WinXDVD, view other free resources The below offers are also available for free in exchange for your (work) email: DearMob iPhone Manager for PC/Mac ($79.95 Value) FREE – Expires today 6/3 The Cybersecurity Control Playbook ($100 Value) FREE – Expires 6/4 The Embedded Linux Security Handbook ($31.99 Value) FREE – Expires 6/4 Teach Yourself VISUALLY Microsoft 365, 2nd Edition ($20 Value) FREE – Expires 6/4 Winxvideo AI V3.0 Lifetime License for PC ($69.95 Valued) FREE – Expires 6/8 Aiarty Image Enhancer for PC/Mac ($85 Value) FREE – Expires 6/8 Solutions Architect's Handbook, Third Edition ($42.99 Value) FREE – Expires 6/10 AI and Innovation ($21 Value) FREE – Expires 6/11 Macxvideo AI ($39.95 Value) Free for a Limited Time – Expires 6/22 The Ultimate Linux Newbie Guide – Featured Free content Python Notes for Professionals – Featured Free content Learn Linux in 5 Days – Featured Free content Quick Reference Guide for Cybersecurity – Featured Free content
    • Ignoring the ads, Google Play Store actually works and has no issues installing apps.
    • 2TB Samsung 990 EVO Plus SSD drops to a great price again by Fiza Ali The 2TB Samsung 990 EVO Plus SSD is currently available on Amazon, Samsung, and Newegg at a great price, only $0.44 higher than its historic low. So, if you have been wanting to upgrade your storage solution, you may want to check it out (purchase links down below). The SSD features NAND memory, delivering sequential read speeds of up to 7,250MB/s and write speeds of up to 6,300MB/s. For random read (QD32), the 2TB version reaches up to 1,000K IOPS, and for random write (QD32), it achieves up to 1,350K IOPS. Furthermore, it carries a Total Bytes Written (TBW) rating of 1,200. The 990 EVO Plus is equipped with Intelligent TurboWrite 2.0 for enhanced performance with large files. Moreover, the drive features a nickel-coated controller that helps manage heat, reduce power usage, and prevent overheating. The SSD supports both PCIe 4.0 x4 and PCIe 5.0 x2, promising faster data transfer speeds and better performance. Additionally, with Samsung's Magician Software, the SSD keeps its firmware up to date, adds extra security, and allows for continuous drive health monitoring. Finally, the Samsung 990 EVO Plus SSD comes with a five year limited warranty, offering long-term reliability and peace of mind. 2TB Samsung 990 EVO Plus SSD: $129.99 (Amazon US) - $129.99 (Samsung US) - $129.99 (Newegg) This Amazon deal is US-specific and not available in other regions unless specified. If you don't like it or want to look at more options, check out the Amazon US deals page here. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. You can also check out other SSD deals here. For hard disk drives, you can head over to our HDD deals section to see if anything from there matches your requirements. Make sure you also browse through Amazon US, Amazon UK, and Newegg US to find some other great tech deals. As an Amazon Associate, we earn from qualifying purchases.
  • Recent Achievements

    • Week One Done
      Adam Todd earned a badge
      Week One Done
    • Contributor
      Ed B went up a rank
      Contributor
    • One Month Later
      moporcho earned a badge
      One Month Later
    • One Month Later
      Parotel earned a badge
      One Month Later
    • Reacting Well
      Cryptecks earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      202
    2. 2
      snowy owl
      146
    3. 3
      ATLien_0
      133
    4. 4
      Xenon
      120
    5. 5
      +FloatingFatMan
      109
  • Tell a friend

    Love Neowin? Tell a friend!