I want to create a database that tracks different projects and their finances. As finances are tracked monthly, a new column has to be created for each month. I could create all tables together at the start, yeah, but I guess it would be bad development strategy. Also I would be guessing the number of months. I will be creating an application to monitor those projects (like graphs and such)... so maybe I could code the program to do it at regular intervals.
Your problem is solved by not adding columns at run-time. You need to apply normalization.
You use two tables:
- PROJECTS containing a PROJECT_ID field (generated ID, guaranteed unique and unchanging)
- PROJECT_FINANCES containing PROJECT_ID (foreign key), PERIOD (date), AMOUNT (number)
Each time you have a new month, you add rows for each project to the PROJECT_FINANCES table. No need to add tables or columns in production.
-
+1 - Good answer to the problem of trying to store data the same way it is used in the application from a visual perspective. – JeffO Aug 7 '13 at 18:34
a new column has to be created for each month
-- why? This really sounds like you've got it designed wrong. The date itself should be a column, and multiple rows (one for each date) rather than one column for each date. – user40980 Aug 7 '13 at 14:51