database design for optional association and temporal

Question

I'm designing a simple database and I'm stuck with one part. I have a Task, Sweep and Spray entities.

       +-------+           
       | Task  |           
       |       |           
       +-+--+--+           
         |  |              
         |  |              
+------+-+  |    +--------+
|Spray |    +----+ Sweep  |
|      |         |        |
+------+         +--------+

Each task is done by either a Sweep or Spray or both. Each Spray and Sweep has few properties that are only valid for particular Task (temporal) and may change over time. For example, spray color is yellow for one task and might be green for another task.

How do I design the tables so that I can query the tasks to see which task was done by which spray and sweep with which properties? Like:

task1: spray green  | sweep auto
task2:      -       | sweep manual
task2: spray black  | sweep manual
task2: spray blue   |       - 

Answer 1

If important attributes of Spray or/and Sweep are changing over the time, I'd rather introduce Spray_version and Sweep_version and make them immutable (every change in Spray or Sweep will create a copy in appropriate *_version table , so old values are never lost). Thus, you refer to *_version tables.

However, in real world I found it's less expensive (from maintenance and readability perspective) to ignore normalization rules and store extra data in Tasks.