I'm new here. This question, to my mind, is allowed by the rules I saw in the "2 minute tour". If I got it wrong, no need for nasty grams. I'll delete if enough people say so. But anyway, I have a bunch of data that I need to organize in a database. This isn't the first I've ever constructed a database, but this might be the most complicated I've done and I want to "do it right". So ....

I have a bunch of simulation runs. To completely specify a simulation run, you need: code, code rev., date of run, begin time, time span, population model, time step, time step method, left boundary, and right boundary.

That specifies a run, but associated with each run is a set of data that comes out of analysis of the results: global max, final value, fitting coeff 1, fitting coeff2, and number of local maxima.

If number of local maxima is greater than zero, then I need to capture that info. A local maximum will have a: value, location, and simulation run.

Well, I have figured out that I need to split off the local maxima into their own table, and link the simulation run of each maximum to the simulation run in the central table.

But what about that central table? An aggregate key would have to have all of the information I listed above for specifying a particular run. Is it a good thing to repeat all of that data in my local maxima table? I thought about establishing a simulation run table that has all of the values needed to specify a run, and then an added unique numerical identifier for each run. Then I could link to this identifier in my local maxima table. This would cut down on the amount of data storage, but would it cause other problems?

Also, as I indicated, some of the data pertaining to a run defines the run while other data comes out of the analysis of the run. Should I split these two types of data into two tables and link with that identifier I mentioned, or should I leave everything all together in one big table?


  • This is really a question of natural vs. surrogate key for primary key. You can see more here and the links pointed to from there. Mar 11 '14 at 17:56

Using a surrogate key as a primary key for tables is a very common approach and it seems to fit your situation quite well. The foreign key in local maxima can then be a single column referencing the surrogate key.

If the relationship between the definition of the run and the outcome of the run is a one-to-one relationship, then you should at first consider having them in the same table, and only split them up if you feel it's more natural, convenient, or better for performance.


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.