• 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

    • This hidden Windows 11 setting makes the system feel a lot faster by Taras Buria As a fan of fancy visuals and a good-looking UI, I upgraded to Windows 11 right after its launch. And while some of my colleagues have a hard time finding legitimate reasons to move to Windows 11, I never looked back. Still, credit where it is due: Windows 10 is still more responsive than Windows 11 (not as Windows 8 was, though). Even when running on a virtual machine, Windows 10 is snappier, and overall, it feels "lighter" than its successor. Animations in Windows 11 feel heavy and a bit wonky even on my pretty capable PC with a Radeon RX 7800 XT and a 144Hz display. While coming back to Windows 10 is not an option, I found a simple solution that not everyone is aware of. My tip of the weekend for Windows 11 users: turn off all animations (genius, I know). As soon as I turned off animations, everything became much snappier: the start menu, virtual desktop switching, context menus (not much faster, but still noticeable), Task View, and other user interface elements. The most notable improvement was virtual desktop switching: instant and without the taskbar going haywire. There are two ways to turn off animation effects in Windows 11. One is in Settings > Accessibility > Visual Effects. Toggle off "Animation effects." The second option is in the legacy "System Properties" applet. Press Win + R, type sysdm.cpl and go to the Advanced tab. Click "Settings" in the Performance section and untick the following options: Animate controls and elements inside windows Animate windows when minimizing and maximizing Animations in the taskbar Of course, you can leave some of those options enabled if you wish. Toggling all three will also turn off the "Animation effects" in the Accessibility settings section. Note that these settings affect not only the general Windows 11 UI but also animations in various apps, which, in turn, can make them feel snappier. I should also add that this simple tweak will not make your computer run faster or generate more FPS in games. It will only address janky animations, which, unfortunately, are still present in Windows 11. While my PC runs perfectly and without performance issues, slow animations play a big role in how it feels. And if you ask me, no animations are better than fancy yet choppy animations (I spoke about it in my recent "Windows 11 still grinds my gears with these 5 things" article). I recently published another guide with five important things every Windows 11 user should do. Therefore, if you want to make the OS run a bit better for you, check out that article here as well. Meanwhile, share your thoughts about Windows 11 animations in the comments.
    • Is this for Black people only? You'd definitely think so from the ad.
    • I have it as an icon in the Start Menu. Close enough for when I need it.
    • Windows 11 Pro with a copy of Office 2021 Pro drops to all-time low price by Steven Parker Today's highlighted deal comes via our Apps + Software section of the Neowin Deals store, where you can save 86% on Windows 11 Pro (for 2 devices) + Microsoft Office Pro 2021. Upgrade your computing experience with Windows 11 Pro. This cutting-edge operating system boasts a sleek new design and advanced tools to help you work faster and smarter. From creative projects to gaming and beyond, Windows 11 delivers the power and flexibility you need to achieve your goals. With a focus on productivity, the new features are easy to learn and use, enhancing your workflow and efficiency. Whether you're a student, professional, gamer, or creative, Windows 11 Home has everything you need to take your productivity to the next level. New interface. easier on the eyes & easier to use Biometrics login*.Encrypted authentication & advanced antivirus defenses DirectX 12 Ultimate. Play the latest games with graphics that rival reality. DirectX 12 Ultimate comes ready to maximize your hardware* Screen space. Snap layouts, desktops & seamless redocking Widgets. Stay up-to-date with the content you love & the new you care about Microsoft Teams. Stay in touch with friends and family with Microsoft Teams, which can be seamlessly integrated into your taskbar** Wake & lock. Automatically wake up when you approach and lock when you leave Smart App Control. Provides a layer of security by only permitting apps with good reputations to be installed Windows Studio Effects. Designed with Background Blur, Eye Contact, Voice Focus, & Automatic Framing Touchscreen. For a true mouse-less or keyboard-less experience TPM 2.0. Helps prevent unwanted tampering Windows 11 Pro also includes a number of productivity-focused features, such as the ability to snap multiple windows together and create custom layouts, improved voice typing, and a new, more powerful search experience. Personal and professional users will enjoy a modern and secure computing experience, with improved performance and productivity features to help users get more done. Only on Windows 11 Pro If you require enterprise-oriented features for your daily professional tasks, then Windows 11 Pro is a better option. Set up with a local account (only when set up for work or school) Join Active Directory/Azure AD Hyper-V Windows Sandbox Microsoft Remote Desktop BitLocker device encryption Windows Information Protection Mobile device management (MDM) Group Policy Enterprise State Roaming with Azure Assigned Access Dynamic Provisioning Windows Update for Business Kiosk mode Maximum RAM: 2TB Maximum no. of CPUs: 2 Maximum no. of CPU cores: 128 Good to know: Length of access: lifetime Redemption deadline: redeem your code within 30 days of purchase Access options: desktop Max number of device(s): 2 (Use one activation key for up to 2 devices) Version: Windows 11 Pro Updates included Click here to verify Microsoft partnership For Windows 10 or Newer! Get All Essential Microsoft Apps for Your PC with This One-Time Purchase This is intended for families and small businesses who want classic Office apps and email. It includes Word, Excel, PowerPoint, Outlook, Teams, and OneNote. A one-time purchase installed on 1 Windows PC for use at home or work. Lifetime license for MS Word, Excel, PowerPoint, Outlook, Teams, & OneNote One-time purchase installed on 1 Windows PC for use at home or work Instant Delivery & Download – access your software license keys and download links instantly Free customer service – only the best support! Microsoft Office Professional 2021 (for Windows) includes: Microsoft Office Word Microsoft Office Excel Microsoft Office PowerPoint Microsoft Office Outlook Microsoft Office Teams Microsoft Office OneNote Microsoft Office Publisher Microsoft Office Access Good to know: ONE-TIME PURCHASE INSTALLED ON 1 DEVICE Redemption deadline: redeem your code within 30 days of purchase Access options: desktop Full versions No subscriptions – no monthly/annual fees Version: 2021 Updates included Here's the deal: This Microsoft Office Pro 2021 + Windows 11 Pro normally costs $438, but this deal can be yours from just $54.97, that's a saving of $383. For full terms, specifications, and license info please click the link below. Use MSO5 when checking out for additional $5 off. Coupon Expires June 29. Get Microsoft Office Pro 2021 + Windows 11 Pro for just $49.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.
    • I'm not a fan of the HP "Smart" app either, but it does work. I just wish I didn't have to log in to use it. HP Color LaserJet Pro MFP 4301
  • Recent Achievements

    • First Post
      Johnny Mrkvička earned a badge
      First Post
    • Week One Done
      viraltui earned a badge
      Week One Done
    • One Month Later
      serfegyed earned a badge
      One Month Later
    • Dedicated
      firey earned a badge
      Dedicated
    • Dedicated
      fettermanj earned a badge
      Dedicated
  • Popular Contributors

    1. 1
      +primortal
      658
    2. 2
      ATLien_0
      224
    3. 3
      Michael Scrip
      224
    4. 4
      Xenon
      146
    5. 5
      +FloatingFatMan
      143
  • Tell a friend

    Love Neowin? Tell a friend!