Hi Folks! I have a tricky question.
The Setup:
The database I'm building will be used to log several types of events, such as meter readings and test results. Each of these events has it's own associated table with various fields:
tbl Meter Readings
Reading ID (PK), Meter ID (FK), Value, Date
tbl Test Results
Result ID (PK), Test ID (FK), Test Location (FK), Test Results, Date
Each of those Foreign Keys links to a "standardized" list, like the list of meters in a facility, or the list of possible tests to perform.
Following so far? The next step is to create a task scheduler for any one of these tasks.
The Task Scheduler
There are some things that all Scheduled Tasks will have in common -
tblTaskScheduler
Scheduled Task ID (PK), Task Type (List), Start Date, Days Between Repeats, Repeat Count
The Task Type tells us whether the task will be a Meter Reading, a Test Taking, or another kind of task.
But in order for the task scheduler to be complete, I will need additional information that varies depending on the task type. If it's a meter reading, which meter should I read? If it's a test, which test should I perform? At what location?
Possible Solutions
There are two ways I thought of to do it:
1) Add an additional field to tblTaskScheduler for each of the possible additional data points. That is, add: Meter ID (FK), Test ID (FK), Test Location (FK). For any one record, only the relevant field would be filled in, leaving lots of blanks in the table.
2) Add tables for each task type and link it 1:1 with the task scheduler. That is:
tblMeterScheduler
Scheduled Task ID (PK, FK), Meter ID (FK)
tblTestScheduler
Scheduled Task ID (PK, FK), Test ID (FK), Test Location (FK)
Any thoughts?
How do these solutions sound? Am I going about this all the wrong way? Any help is much appreciated!