• 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

    • Bet Apple things they invented naming stuff with the year
    • My home system is a 5800X (upgraded from 2700X) with a 7800 XT. I can't comment as to why you feel so strongly about the differences, but I have used both Windows 10 and 11 for literally thousands of hours each; I'd guess over 10,000 hours on Windows 10 and maybe half that on Windows 11. Earlier builds of Windows 11 had some pretty big UI lag issues, which did annoy me, but not enough to go back to Windows 10. 23H2 and forward have corrected all those issues for me and I have no complaints at all at this time. Let me clarify what I meant by "Windows 11 runs perfectly fine." I don't mean it is merely acceptable; I mean that I don't perceive a difference between it and Windows 10. I chose the word "fine" because I don't believe that either 10 or 11 are perfect in that area and both have the rare UI hiccup, but in my experience, their responsiveness is at the same level.
    • WinToUSB 9.9 by Razvan Serea WinToUSB allows you to install and run a fully-functional Windows on external hard drive, USB flash drive or Thunderbolt drive. It is so easy and efficient, with just 3 steps and a few minutes, you can create your first portable Windows 11/10/8/7 or Windows Server directly from an ISO, WIM, ESD, SWM, VHD, VHDX file or CD/DVD drive, or you can clone currently running Windows installation to USB or Thunderbolt drive as portable Windows. WinToUSB also supports creating Windows installation USB drive from Windows 11/10/8/7 and Windows Server installation ISO, with it you can install Windows from the USB drive easily. Note: The WinToUSB Free Edition is solely intended for non-commercial, private, and personal use on home computers. It should be noted that technical support is not available for the free edition. Use of WinToUSB Free Edition within any organization or for commercial purpose is strictly prohibited. WinToUSB key features include: Creation of Windows To Go from ISO, WIM, ESD, SWM, VHD(X) or DVD drive.Improved Clone Windows 11/10/8/7 to USB/Thunderbolt drive as portable Windows. Creation of Windows To Go on Non-Certified Windows To Go USB drive. Encrypt Windows To Go with BitLocker to keep your data safe. Creation of Windows installation and bootable WinPE USB drive with BIOS & UEFI support. Download Official Windows 11/10/8.1 ISO file from Microsoft. Use any edition of Windows 11/10/8/7 and Windows Server 2022/2019/2016/2012/2010 to create Windows To Go USB drive. Windows To Go (Portable Windows) Creator WinToUSB allows you to install & run fully-functional Windows on an external HDD/SSD, USB flash drive or Thunderbolt drive, which means you can carry the portable Windows drive to anywhere and use it on any computer. Faster installation and cloning speed compared to competing products Support any edition of Windows 11/10/8/7 and Windows Server Creation of Windows To Go from ISO, WIM, ESD, SWM, VHD(X) or CD/DVD drive Clone currently running Windows to USB/Thunderbolt drive Creation of Windows To Go on Non-Certified Windows To Go drive Create BitLocker encrypted Windows To Go Workspace Create portable Windows for Intel-based Mac computers Support for creating VHD(X)-based Windows To Go Windows Installation USB Creator WinToUSB releases a feature called "Windows Installation USB Creator" which allows you to create a Windows installation USB drive from a Windows 11/10/8/7/vista/2022/2019/2016/2012/2008 installation ISO file with a few simple steps, with this feature you can create a bootable Windows installation USB drive to install Windows on both Traditional BIOS and UEFI computers by using the same USB drive. Bypass Windows 11 system requirements (TPM 2.0, Secure Boot, Minimum hardware and Microsoft account) Install Windows on both BIOS and UEFI computers by using the same USB drive Windows PE Bootable USB Creator This feature allows you to create a bootable Windows PE USB drive, it can help you transfer the contents of a Windows PE ISO file to a USB drive and make the USB drive bootable, and this feature supports the creation of a bootable WinPE USB driver that supports both Traditional BIOS and UEFI computers. WinToUSB 9.9 changelog: Added option to disable BitLocker automatic drive encryption when creating Windows installation USBs Fixed bug: setup.exe cannot bypass the Windows 11 system requirements Fixed bug: Cloned Windows ARM64 cannot start properly Fix other minor bugs Download: WinToUSB 9.9.0 | 28.7 MB (Freeware) Links: Home Page | Free vs Pro Comparison | Screnshots Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Maybe you don't realize this...but everything you said agreed with me. Yes, many tech outlets reported on Ryzen 9000 issues prior to 24H2, which I already addressed, and as I already said, that issue only existed for a few short months. Ryzen 9000 was released the same quarter of 2024 as 24H2. So again...months, not years. I also already said 24H2 showed some minor improvements on older Ryzen CPU. The article you posted agrees with me, and even says the improvements were only 3-5%, which is even more petty an amount than I estimated. If you want to fuss on the 3-5% numbers, then yes, I will grant you that was an issue for an extended amount of time. In my opinion, that is such a small amount it isn't worth fussing over, but you are welcome to a different option. However, if that was your point, then you didn't make that point in good faith, because you highlighted Ryzen 9000 so much, which had a FAR bigger and FAR shorter issue, it's really a very different conversation.
  • 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
      252
    3. 3
      snowy owl
      251
    4. 4
      ATLien_0
      228
    5. 5
      +Edouard
      191
  • Tell a friend

    Love Neowin? Tell a friend!