0

I have two identical tables, one is for rows which are in a queue, and one is where I move my rows once they have been processed.(thousands of rows processed daily)

In my jobs table, I store ids to other tables and retrieve the values when needed through join statements, However once a row is processed, and I move this row into the final table, I am wondering whether I should just store the actual values here of what the ids refer to, instead of the ids like in the jobs table.

This final table is used encase I need to look up what happened in the past, a potential problem with storing just the id here is that if I ever change the value in the parent table(what the id references), my final table will be referencing incorrect values as the value has changed since its original entry.

I have not managed to come across this scenario in books yet, so I do not know terminology for this, Or if/how its handled.

I am also curios if perhaps I am handling the referenced data completely wrong? Perhaps I should not edit my original values once they are stored and every time I need to edit them, I enter a new row and start referencing that id? But this also doesn't sound like a good method.

Thanks in advance.

1

Your queue records depend on values of other tables referenced via foreign keys. The values you lookup in this reference tables is about to change over time.

You want to be able to check on processed records with wich values they were processed, especially which lookup values where used in the processing that were valid at the time of processing.

In that case you must copy the value in your archive/log table, otherwise as you pointed out your processed-table references the current lookup values, not the ones that were valid at the time of processing.

Versioning your lookup values based on start_dat,end_date depends on if you need the history of these tables even when nothing was processed with these values. If you only need the values if there are processed records i would go with the denormalized log table structure. Much simpler.

| improve this answer | |
  • Thanks, So far it seems this is the best way, I will wait a while to see if anybody else has anything to say about the issue. – cecilli0n Feb 13 '14 at 19:14

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.