• 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

    • Or…because it makes it obvious what they are referring to. Imagine if Microsoft called it Visual Studio 22.
    • Ghost Downloader 3 v3.5.13 by Razvan Serea Ghost Downloader 3 is a high-performance download manager for Windows, featuring AI-enhanced acceleration, intelligent multi-threading, and smart file segmentation. It supports download resumption via .ghd files, global speed limits, and clipboard monitoring for quick link capture. The interface is built with Fluent Design, offering a modern and smooth user experience. Users benefit from advanced features like sparse file support, system tray integration, proxy configuration, secure SSL options, and automatic filename recognition. Ghost Downloader 3 is ideal for users seeking speed, efficiency, and customization. Core Download Features AI-Powered Acceleration: Experimental feature that dynamically increases thread count (up to 253) to maximize bandwidth usage. Intelligent Segmentation: Divides downloads into multiple parts for parallel processing, supporting breakpoint resume via .ghd files. Dynamic Thread Allocation: Automatically splits faster segments to create new threads, enhancing download speeds. Network & Proxy Support Global Speed Limiting: Allows setting a maximum download speed to manage bandwidth usage. Proxy Support: Compatible with SOCKS5, HTTP, and HTTPS proxies, including auto-detection features. SSL Certificate Verification: Optional SSL verification for secure downloads. System Proxy Detection: Automatically detects system proxy settings across Windows, Linux, and macOS. Windows-Specific Features Fluent Design UI: Modern interface with support for Mica, Acrylic, and Aero background effects. Toast Notifications: Supports Windows 10 (1709+) native notifications. Window Border Accent Colors: Enhanced visual integration with Windows 11. Application Features Smart Filename Recognition: Automatically identifies and decodes filenames from HTTP headers, URL parameters, or paths. Sparse File Support: Utilizes sparse file technology on supported file systems (e.g., NTFS) for efficient disk space allocation. Clipboard Monitoring: Optionally monitors clipboard for download links to facilitate quick task additions. Drag-and-Drop and Paste-to-Add: Supports intuitive methods for adding download tasks. Task Management: Features batch addition, per-task thread customization, pause/resume/cancel options, and hash verification (MD5, SHA1, SHA256). System Integration System Tray Support: Minimizes to system tray with background download capabilities. Automatic Startup and Task Resumption: Configurable to launch on system startup and resume unfinished downloads. Single Instance Enforcement: Prevents multiple instances from running simultaneously. Automatic Update Checks: Optionally checks for new versions on startup. Ghost Downloader 3 v3.5.13 changelog: Improved shared memory handling for macOS ARM by @cy920820 in #176 Fixed empty thread creation bug by @Alpha-Qian in #184 Optimized automatic speed boost feature by @Alpha-Qian in #183 Optimized download engine code by @XiaoYouChR Notes: Ghost Downloader may trigger a warning or show a hit on VirusTotal, but if only a few antivirus engines flag it, it’s likely a false positive. The app is open source, so you can inspect the code yourself for peace of mind. If you want to use Ghost-Downloader-3 on Windows 7, please download the version v3.5.8-Portable. Download: Ghost Downloader 64-bit | Portable 64-bit | ~50.0 MB (Open Source) Download: Ghost Downloader ARM64 | Portable ARM64 Links: Ghost Downloader Home Page | MacOS / Linux | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Microsoft Edge is getting new media control center, AI-powered history search, and more by Taras Buria Microsoft has updated Edge Beta to version 138. Its changelog contains many new features and improvements, some of which are quite interesting. 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. The media control center button will show up on the toolbar (a button with a note icon), allowing you to launch picture-in-picture, cast media to other devices, control music or video, and more. Next is an AI upgrade for the History section. It now uses artificial intelligence to find relevant websites in your history. From now on, you do not need to remember the exact name of the website or a word from the page's name. You can find what you need using synonyms, phrases, or words with typos. Microsoft says this feature uses an on-device model that is trained on your data and does not send any information to Microsoft. AI-powered history search is rolling out gradually to Edge Beta insiders. Another interesting change is the dynamic Settings menu. If the browser slows down, a notification from Performance and Extensions Detector will show up in the menu to help users learn about the built-in methods to improve performance. Like AI-powered history search, this feature is rolling out gradually. Other changes in Microsoft Edge 138 Beta include the following: Microsoft Edge can now use your primary work profile by default when opening external links instead of the last used profile (rolling out gradually). Autofill received a new toggle that lets users consent to Microsoft Edge collecting web form field labels to improve suggestion accuracy. Note that this feature does not send user-entered data; it only sends field labels. Enterprise users can now view sensitivity labels in MIP-protected PDFs. Microsoft 365 Copilot Chat Summarization is now available in the context menu (gradual rollout). As usual, each major browser release delivers policy updates. You can find the complete list of policy changes in the release notes. Microsoft Edge Beta 138 is now available for download. You can get it on the official Edge Insider website. Version 138 will be available in the Stable Channel on the week of June 26, 2025.
    • Winxvideo AI V3.0 lifetime license for PC (worth $69.95) free offer ends June 8 by Steven Parker Claim your complimentary license worth $69.95 for free today, before the offer ends on June 8. Powered by CineAI technology, Winxvideo AI can revitalize your video/photo, whether it’s old, low in resolution, noisy, or blurry; and it also can upscale, stabilize, convert, compress, record, and edit 4K/8K/HDR videos smoothly and efficiently, achieving cinema-grade visuals in every frame. On occasion of WinXDVD's 19th anniversary, everyone here can get a lifetime license of Winxvideo AI V3.0 for free as a thank-you gift. Grab the chance to enhance and process your multimedia content effortlessly, at no cost. Claim your free license before 6/8/2025. What you can do with Winxvideo AI: AI video enhance: upscale low-quality video to 1080P or 4K; stabilize shaky video shot by Gopro, drone, handheld camera or mobile phone; increase frame rate up to 480 fps. Smoother, steadier and crisper playback. AI image enhance: upscale and restore old, blurry image to 4K/8K/10K with real details. Convert any video and music to 420+ formats for various use. GPU accelerated. Compress 8K/4K/HD video to smaller size with highest quality. Record tutorials, gameplay videos, and more from screen, webcam, or both. Download: save 4K/1080P video, music, playlist, channel, m3u8, etc., from 1000+ sites for offline playback. Edit video: cut, crop, merge, flip, rotate video; add effect/watermark/subtitle, make GIF, change playback speed, etc. Free offer expires June 8. 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! Download Winxvideo AI V3.0 Lifetime License for PC (worth $69.95) now FREE Offered by Wiley, view other free resources The below offers are also available for free in exchange for your (work) email: Winxvideo AI V3.0 Lifetime License for PC ($69.95 Value) 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 Unruly: Fighting Back when Politics, AI, and Law Upend [...] ($18 Value) FREE - Expires 6/17 SQL Essentials For Dummies ($10 Value) FREE – Expires 6/17 Continuous Testing, Quality, Security, and Feedback ($27.99 Value) FREE – Expires 6/18 VideoProc Converter AI v7.5 for FREE (worth $78.90) – Expires 6/18 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 We post these because we earn commission on each lead so as not to rely solely on advertising, which many of our readers block. It all helps toward paying staff reporters, servers and hosting costs. Other ways to support Neowin The above deal not doing it for you, but still want to help? Check out the links below. Check out our partner software in the Neowin Store Buy a T-shirt at Neowin's Threadsquad Subscribe to Neowin - for $14 a year, or $28 a year for an ad-free experience Disclosure: An account at Neowin Deals is required to participate in any deals powered by our affiliate, StackCommerce. For a full description of StackCommerce's privacy guidelines, go here. Neowin benefits from shared revenue of each sale made through the branded deals site.
  • 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
      495
    2. 2
      +FloatingFatMan
      253
    3. 3
      snowy owl
      251
    4. 4
      ATLien_0
      228
    5. 5
      +Edouard
      191
  • Tell a friend

    Love Neowin? Tell a friend!