1

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.

Improve this question
7
  • 6
    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
  • Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. Also see How to Ask – gnat Aug 7 '13 at 14:54
  • its not multiple columns for dates....we will store data about the finance for Some 600 projects in that column....like project A month Y amnt M project B month Y amnt N project C month Y amnt L....etc hope you get the msg! :) – Kinda New Aug 7 '13 at 14:55
  • 2
    We get the message, but you should really post your data model here; perhaps draw.io can help, @MichaelT is right, you have your model wrong. What database server are you using? Is this by chance an access database? – Jimmy Hoffa Aug 7 '13 at 15:09
  • 1
    @MarjanVenema it just sounds like access may be the tool in use, and if so we can solve two problems here: Help him get the design right and instruct him to not use access – Jimmy Hoffa Aug 7 '13 at 17:35
12

Your problem is solved by not adding columns at run-time. You need to apply normalization.

You use two tables:

  1. PROJECTS containing a PROJECT_ID field (generated ID, guaranteed unique and unchanging)
  2. 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.

Improve this answer
1
  • +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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.