0

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   |       - 
2
  • What values does Task hold that are common to both Spray and Sweep? Dec 5, 2014 at 20:58
  • @JohnM, nothing.
    – Sam R.
    Dec 5, 2014 at 21:04

1 Answer 1

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.

1
  • I was thinking the same but I thought there might be better way than de-normalization. But what about being optional? How to avoid storing null if either Spray or Sweep is not present?
    – Sam R.
    Dec 6, 2014 at 4:48

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.