• 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

    • @Raze Bold it boy. (I admit, we all did it from time to time..)
    • Fan Control V227 by Razvan Serea Fan Control is a powerful and versatile portable utility that allows you to monitor, control and customize the fans of your GPU and CPU to keep your machine cool and running smoothly. Fan Control supports a wide range of devices and hardware configurations, giving you complete control over your computer's cooling system. Fan Control backend is mainly based on LibreHardwareMonitor, an open source fork of the original OpenHardwareMonitor. This means that hardware compatiblity is entirely open for anyone to contribute, and doesn't rely on a single developer who may stop caring at some point. Combined with the plugin system, Fan Control is unlocked for many generations of hardware to come. Main features Guided setup process on first launch Save, edit and load multiple profiles Change the theme and color of the application. Multiple temperature sources ( CPU, GPU, motherboard, hard drives... ) Multiple fan curve functions, including a custom graph Mix fan curves or sensor togethers (max, min, average) Low resource usage Advanced tuning with steps, start %, stop %, response time and hysteresis FanControl V227 changelog: Allow decimal with hysteresis values Radeon Pro support through ADLX Fix a bug when cancelling the graph editing dialog Update LibreHardwareMonitorLib Download: FanControl V227 | Installer ~15.0 MB (Open Source) View: Fan Control Homepage | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Lol tf you are talking about. People are PAYING by using this app exclusively, just indirectly. Do you have any idea how much facebook charges for api access to WhatsApp which has become more or less a monopoly in e-commerce.
    • My kid tried, so I took her to Microcenter and showed her the differences in price as well as storage and specs. We bought a Windows machine. She loves it, and is off to college this fall with her gaming level windows laptop at the price of a Mac that came with a 512GB SSD, and half the RAM.
    • Hasleo Backup Suite Free 5.4.2.1 by Razvan Serea Hasleo Backup Suite Free is a free Windows backup and restore software, which embeds backup, restore and cloning features, it is designed for Windows operating system users and can be used on both Windows PCs and Servers. The backup and restore feature of Hasleo Backup Suite can help you back up and restore the Windows operating systems, disks, partitions and files (folders) to protect the security of your Windows operating system and personal data. The cloning feature of Hasleo Backup Suite can help you migrate Windows to another disk, or easily upgrade a disk to an SSD or a larger capacity disk. System Backup & Restore / Disk/Partition Backup & Restore Backup Windows operating system and boot-related partitions, including user settings, drivers and applications installed in these partitions, which ensures that you can quickly restore your Windows operating system once it crashes. Viruses, power failure, or other unknown reasons may cause data loss, so it is a good habit to regularly back up the drive that stores important files, you can at least recover lost files from the backup image files in the event of a disaster. System Clone / Disk Clone / Partition Clone Migrate the Windows operating system from one disk to another SSD or larger disk without reinstalling Windows, applications and drivers. Clone entire disk to another disk and ensure that the contents of the source disk and the destination disk are exactly the same. Clone a partition completely to the specified location on the current disk or another disk and ensure that the data will not be changed. File Backup & Restore Back up specified files(folders) instead of the entire drive to another location to protect your data, so you can quickly restore files(folders) from the backup image files when needed. Incremental/Differential/Full Backup Different backup modes are supported, you can flexibly choose data protection schemes, which can improve backup performance and save storage space while ensuring data security. Delta Restore Delta restore uses advanced delta detection technology to check the changed blocks on the destination drive and restore only the changed blocks, so it has a faster restore speed than the traditional full restore. Universal Restore This feature can help us restore the Windows operating system to computers with different hardware and ensure that Windows can work normally without any hardware compatibility issues. Hasleo Backup Suite 5.4.2.1 changelog: The program crashes when sending emails Application notifications cannot be displayed in the Windows Notification Center Updated Italian and German translations Fixed other minor bugs Download: Hasleo Backup Suite 5.4.2.1 | 33.9 MB (Freeware) Links: Hasleo Backup Suite Website | Hasleo Backup Suite Guide | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • Recent Achievements

    • Contributor
      GravityDead went up a rank
      Contributor
    • Week One Done
      BlakeBringer earned a badge
      Week One Done
    • 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
  • Popular Contributors

    1. 1
      +primortal
      660
    2. 2
      ATLien_0
      266
    3. 3
      Michael Scrip
      235
    4. 4
      Steven P.
      164
    5. 5
      +FloatingFatMan
      150
  • Tell a friend

    Love Neowin? Tell a friend!