The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
Help me pretend like I have a database hat to put on?
schattenjaeger
Registered User regular
Registered User regular
I think I almost have a handle on this, I just want to make sure I am not doing something stupid. I am building a database in Access that needs to contain annual cost information for numerous programs. Easy, right? Well a few wrinkles.
First, each program was observed at different points in time, from 1-5 times, and the observations are titled, let's say A, B, etc. I need the costs for each year, for each program, at each observation. The programs don't span the same years, or the same number of years.
There are program attributes that can change from observation to observation, so I already have one table that has both program ID and observation as the composite primary keys. That seemed like the right thing to do because it prevented accidentally entering data for the same thing twice.
Last wrinkle, there is a cost structure, about 40 items like programming, design, management, test, etc. That is shared between all the programs, though not all programs used all 40. I need the cost for each program at each observation for each of those items for every year :-)
So what I am thinking is the best way is to have a table that's fields are program ID, observation, year, cost element, and cost. This is much simplified from some of my more...ridiculous solutions. It seems like this is pretty normalized. The main thing that bothers me is that in that table, all the fields except cost would be part of the primary key. Is that fine? I also figure it is best to have the cost structure in a separate table and use cost element IDs instead of names like "management."
First, each program was observed at different points in time, from 1-5 times, and the observations are titled, let's say A, B, etc. I need the costs for each year, for each program, at each observation. The programs don't span the same years, or the same number of years.
There are program attributes that can change from observation to observation, so I already have one table that has both program ID and observation as the composite primary keys. That seemed like the right thing to do because it prevented accidentally entering data for the same thing twice.
Last wrinkle, there is a cost structure, about 40 items like programming, design, management, test, etc. That is shared between all the programs, though not all programs used all 40. I need the cost for each program at each observation for each of those items for every year :-)
So what I am thinking is the best way is to have a table that's fields are program ID, observation, year, cost element, and cost. This is much simplified from some of my more...ridiculous solutions. It seems like this is pretty normalized. The main thing that bothers me is that in that table, all the fields except cost would be part of the primary key. Is that fine? I also figure it is best to have the cost structure in a separate table and use cost element IDs instead of names like "management."
0
Posts
One table for cost element and cost (with an associated ID).
One table for Program ID and name (might not be necessary)
Then a final table for actually recording the observations, for fields would be something like:
fkProgram, Year, Observation, fkCostItem
If you know how to do access forms that table would be really easy to fill out. Drop down for Program, enter in year, enter Observation period, drop down for cost item.