Tricky Relationships in a Task Scheduler

[igglebop]

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!

[June7]

For option 1, are those the only fields that differentiate for the type of task? Do the 'other' types have additional information?

I doubt there is any such thing as a perfectly normalized database. It is a balancing act between normalization and ease of data entry/output. You just have to decide where the balancing point is.

Do you need to associate the task schedule record with the results/reading record?

[igglebop]

For option 1, are those the only fields that differentiate for the type of task? Do the 'other' types have additional information?

No, there are a number of other task types, and other associated fields, that I left out for simplicity.

Do you need to associate the task schedule record with the results/reading record?

Not necessarily, though it seems like a good thing to do. The Task Schedule record needs to have enough information to generate an instance in the appropriate log. If the only additional field were an ID (such as a Test ID or a Gauge ID), I could just leave off the relationship and use the same field for both. Then I would write some code that says, "Is this task a Test? Then look in the 'ID' field to know which test. Is it a Gauge? Then use the 'ID' field to determine which gauge." It just seems like that solution would be bad design.