Overview
An incident database that will have a number of columns holding an ID for a record held in a lookup table.
The problem I'm trying to solve
I need to come up with a robust solution to manage historical data where some fields hold lookup IDs. I've listed my proposed solutions as well as alternatives. I would like to know from other developers if they manage these scenarios in a similar way in their projects. Perhaps you have a better approach?
Database: Oracle 10g
Column: Department_name
Scenario: The department name can change X amount of times through the year. The business have a need to report data for all their departments but wish to see incidents under their respective department names as it was at the time of the incident.
Proposed solution: When setting up an entry in the department name lookup table, set a start and end date value. Using a view, create a calculated field based on the incident date to access the correct department name at any given point in time.
Pros: With a little bit of defensive coding it would enable self service by selected users to manage their static data via a GUI without any additional database changes. On the fly changes can be done e.g. changing the name completely. No DBA support is required.
Cons: Potentially an expensive operation given the volume of lookups/calculations being done over a large dataset.
Alternative solution: Simply use and insert the plain text value of the department name. The drawbacks here would be that DBAs are needed for adhoc requests to change/update values, potentially targeting specific date ranges and missing some records in error. There would also be increased table space consumption.
Column: Assigned_Technician_ID
Scenario: An incident will have one technician assigned where the ID of the technician will be stored. A lookup table will hold a 'current' list of all available technicians. As people leave the business the list has to be refreshed and obsolete technicians removed. This is to keep the number of values in the dropdowns to a minimum. The business will still want to see which technicians were assigned on all of their incident data.
Solution: Instead of deleting an entry from the technician lookup table, mark the entry with a flag which denotes 'archived/deleted'. This flag would act as a filter on the GUI dropdowns to remove unwanted entries.
Pros: The lookup table would only consist of the technician's UID from the employee table. Therefore if business requirements change it would be easy to render any attributes of a technician in the main view e.g. full name or employee number and so on.
Cons: As in the previous example the lookups could potentially be an expensive operation on a large dataset. Additional work would be required on the GUI side in regards to business logic and design. Specifically how to manage dropdowns lists when the original entry has been 'archived'.
Alternative Solution: As in the previous example above, just use the plain text value. Drawbacks here would be greater consumption of table space and less flexible with changing business requirements.
See Question&Answers more detail:os