Jump to content
Posted 26 December 2012 - 17:39
Posted 29 December 2012 - 14:28
Posted 29 December 2012 - 16:57
Posted 29 December 2012 - 18:05
revenue_2011 revenue_2012 etc
is almost always a bad idea,
Posted 10 January 2013 - 23:54
You're not giving us much information on the details, so it will be difficult to give you a schema.
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.
Posted 11 January 2013 - 00:20
Posted 11 January 2013 - 17:03
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.
LineItemID int primary key identity,
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.
CompanyID int primary key identity,
--This is your primary table, the table where you will store the financial info. It links to the above tables.
FinanceID int primary key identity,
Year smallint, --assuming you're aggregating by year - you should only have one line per line item per company per unit of time
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.