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.
7
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