• 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

    • Firefox, for privacy, wide range of plugins without having to suffer the latest Google manifest restrictions and when used on Android for the mobile browser plugins like an ad-blocker, sadly that option isn't available to me now I've swapped to iOS as outside of the EU Firefox mobile is just a reskin of Safari
    • Ok will give that a shot soon as wake up in morning.     Hopefully does restore it working right  
    • Try doing a reset/repair on the Windows store: To reset the Microsoft Store on Windows 11, press the Windows key + I to open Settings, go to Apps & features, find Microsoft Store, click on it, select Advanced options, and then click the Reset button. This will clear the cache and restore the app to its default state, which can help resolve issues.
    • Microsoft's new Exchange Message Trace: What admins need to know before September by Paul Hill Microsoft has just announced the general availability of the new Message Trace in the Exchange admin center (EAC) in Exchange Online for its worldwide (WW) customers. The Redmond giant said that it’ll begin rolling it out in mid-June and complete the rollout in July. Message Trace in the Exchange Admin Center for Exchange Online is a tool that lets admins trace which path emails took as they traveled through the Microsoft 365 organization. It lets admins see if emails were received, rejected, or deferred. It is helpful for troubleshooting mail flow issues and validating policy changes. To get started with the new Message Trace, admins can access it by going to the Exchange admin center > Mail flow > Message Trace. While the Windows-maker has received positive feedback during the Public Preview, you can still provide your thoughts through Exchange admin center > Give Feedback. In addition, Microsoft will continue to maintain the old Message Trace user experience in Exchange admin center and cmdlets for several months to ease the transition, however, they will be deprecated for WW customers starting from September 1. The Reporting Webservice support for Message Trace data will also begin deprecating on this date. A side note to mention here is that this timeline only applies to the WW environment and doesn’t affect GCC, GCC-High, DOD, or other sovereign clouds. More information about the switch over for those will be provided in the second half of the year. Who it affects, and how These changes need to be noted by Exchange Online administrators and IT professionals as those are the people who will be directly affected. Specifically, it will affect anyone managing mail flow and troubleshooting email delivery in Exchange Online. Those who are affected will have to get switched over to the new Message Trace before Microsoft starts deprecating features in several months time. Admins will want to act promptly to avoid any unforeseen issues that could arise. Another detail that admins should be aware of is that scripts that rely on the older “Get-MessageTrace” or “Get-MessageTraceDetail” cmdlets will break on September 1. To address this, admins will need to update their scripts to use the new “Get-MessageTraceV2” and the “Get-MessageTraceDetailV2” cmdlets. Finally, any admins out there using the Reporting Webservice for Message Trace data will also need to make a change. They will need to shift to the new Message Trace PowerShell cmdlets. Why it’s happening Microsoft has been working on a new Message Trace experience, incorporating feedback from the Public Preview phase, to improve its design and performance. The switch gives Microsoft the opportunity to standardize and modernize admin interfaces and the underlying technologies. What to watch for While September 1 may seem like a long way away, fixing any issues, such as scripts due to deprecations, could take some time. Any admins managing the affected items need to ensure they deal with affected components in a timely manner. In terms of documentation, Microsoft has so far only released the Public Preview document which highlights the changes between the old and new versions. Microsoft says that it will publish cmdlet documentation for the new Message Trace cmdlets by the time of the general availability, so admins should look out for that.
    • Microsoft PC Manager 3.17.2.0 (Offline Installer) by Razvan Serea With Microsoft PC Manager, users can easily perform basic computer maintenance and enhance the speed of their devices with just one click. This app offers a range of features, including disk cleanup, startup app management, virus scanning, Windows Update checks, process monitoring, and storage management. Microsoft PC Manager key features: Storage Manager- easily uninstall infrequently used apps, manage large files, perform a cleanup, and set up Storage Sense to automatically clear temporary files. Health Checkup feature -scans for potential problems, viruses, and startup programs to turn off. It helps you identify unnecessary items to remove, optimizing your system's performance. Pop-up Management - block pop-up windows from appearing in apps. Windows Update - scans your system for any pending updates. Startup Apps - enable or disable startup apps on your PC, allowing you to optimize your system's startup performance. Browser Protection - rest assured that harmful programs cannot alter your default browser. Also enables you to change your default browser. Process Management - allows you to conveniently terminate any active process, ensuring optimal system performance and resource utilization. Anti-virus protection - Fully integrated with Windows Security. Safeguard your PC anytime. Quick Steps: Download Microsoft PC Manager Offline Installer (APPX/MSIX) with Adguard Adguard serves as a third-party online service, offering a user-friendly method for directly downloading appx, appxbundle, and msixbundle files from the Microsoft Store. Official download links will be generated for both the app's various versions and its dependency packages. How to download Microsoft PC Manager Offline Installer (APPX/MSIX) 1. Initially, you must find the app URL within the Microsoft Store. Access the Microsoft Store via your browser and search for "Microsoft PC Manager". Once located, copy the app URL, which includes the product ID, either from the address bar or from the provided link below. https://apps.microsoft.com/detail/9PM860492SZD 2. Now paste the app URL into the designated area, then click the check mark button to produce a direct download link. 3. To download, right-click the relevant link and select “Save link as…” from your browser's menu. Occasionally, Microsoft Edge may flag the download as insecure. In such cases, consider utilizing alternative browsers such as Google Chrome or Firefox to successfully complete the download. Microsoft PC Manager is a completely free tool optimized exclusively for use on Windows 10 (version 1809 or newer) and Windows 11. Download: Microsoft PC Manager 3.17.2.0 | from Microsoft Store View: Microsoft PC Manager Home Page Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • Recent Achievements

    • Week One Done
      Leonard grant earned a badge
      Week One Done
    • One Month Later
      portacnb1 earned a badge
      One Month Later
    • Week One Done
      portacnb1 earned a badge
      Week One Done
    • First Post
      m10d earned a badge
      First Post
    • Conversation Starter
      DarkShrunken earned a badge
      Conversation Starter
  • Popular Contributors

    1. 1
      +primortal
      261
    2. 2
      snowy owl
      158
    3. 3
      +FloatingFatMan
      145
    4. 4
      ATLien_0
      140
    5. 5
      Xenon
      131
  • Tell a friend

    Love Neowin? Tell a friend!