• 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

    • No, you have no idea, if there is a cable.it is prone to that unless it is shielded.
    • Would be cool if the user can drag it around like a widget. Set a boundary so that it cannot go outside the monitor.
    • missing feew things like CPU temp, hopefully they add an option to customize what would i want displayed or not, but as a default and easy msi afterburner alternative its great and it even work on destiny 2
    • Microsoft Visual Studio Professional 2022 for Windows reaches all-time low price by Steven Parker Today's highlighted deal comes via our Neowin Deals store, where you can save 94% on a copy of Microsoft Visual Studio Professional 2022 for Windows. Visual Studio Professional 2022 is a fully featured development environment that developers around the world know and love. Our first 64-bit IDE makes it easier to work with even bigger projects and more complex workloads. Enhance your productivity, write high-quality code, and re-imagine collaboration with an advanced suite of tools and built-in integrations to tackle the most challenging development workflows and deliver innovative apps. Build across languages & platforms Craft cross-platform mobile and desktop apps with .NET MAUI Build responsive Web UIs in C# with Blazor Build, debug, & test .NET and C++ apps in Linux Use hot reload capabilities across .NET and C++ apps Edit running ASP.NET pages in the web designer view Type less, code more with IntelliCode Understand your code context: variable names, functions, & the type of code you’re writing Complete a line or block of code Get a list of next best options, helping you code more rapidly & accurately Gain deep insights into your code with CodeLens Reveal crucial information like recent changes, authors, tests, and commit history Make informed decisions with a comprehensive overview of your codebase Collaborate seamlessly with Live Share's real-time collaboration sessions Speed up your team's edit & debugging cycles with personalized sessions, access controls, and custom editor settings Ensures everyone's code stays consistent Good to Know ONE-TIME PURCHASE INSTALLED ON 1 DEVICE Redemption deadline: redeem your code within 30 days of purchase Access options: desktop & mobile Version: Professional 2022 Supported languages: Visual Studio is available in English, Chinese (Simplified), Chinese (Traditional), Czech, French, German, Italian, Japanese, Korean, Polish, Portuguese (Brazil), Russian, Spanish, & Turkish Updates included Microsoft Visual Studio Professional 2022 for Windows normally costs $499, but this deal can be yours for just $27.97, that's a saving of $471. For full terms, specifications, and license info, click the link below. Use MSO8 when checking out for additional $8 off. Coupon Expires June 29. Get Microsoft Visual Studio Professional 2022 for Windows for just $19.97, or learn more Although priced in U.S. dollars, this deal is available for digital purchase worldwide. We post these because we earn commission on each sale 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 Whitelist Neowin by not blocking our ads Create a free member account to see fewer ads Make a donation to support our day to day running costs Subscribe to Neowin - for $14 a year, or $28 a year for an ad-free experience Disclosure: Neowin benefits from revenue of each sale made through our branded deals site powered by StackCommerce.
    • My kids each had a Microsoft account that could log into the family computers, but they did not have admin access, and we didn't have a general guest account or anything like that, so it worked pretty well. You are right, with enough technical knowledge, you can find ways around anything. I still remember I was helping my 10-year-old son with something on his computer and noticed that his account had admin rights. I asked how he did it, and he said he found the BitLocker recovery key, decrypted the drive, then booted a Linux USB and edited the security hive. I told him that if he figured all that out on his own that he deserved admin rights :-)
  • Recent Achievements

    • Week One Done
      Helen Shafer earned a badge
      Week One Done
    • First Post
      emptyother earned a badge
      First Post
    • Week One Done
      Crunchy6 earned a badge
      Week One Done
    • One Month Later
      KynanSEIT earned a badge
      One Month Later
    • One Month Later
      gowtham07 earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      665
    2. 2
      ATLien_0
      270
    3. 3
      Michael Scrip
      219
    4. 4
      Steven P.
      166
    5. 5
      +FloatingFatMan
      160
  • Tell a friend

    Love Neowin? Tell a friend!