0

Basically I need an scalable efficient way to select all scheduled items so it made sense that the main table is the schedule. The problem is that all the operation tables have different properties

I created a diagram which is obviously wrong but demonstrates what I am trying to achieve.

I was thinking about having the join on the other side so that all three operations pointed to the schedule, but if I do that then to get a complete schedule I have to aggregate all the operation tables. What if I have loads more in the future?

Another option was to have the delivery, maintenance, loading columns in the schedule table but again there could be loads more operations. I'm just wondering of I am missing something or is this the best option?

db diagram

1 Answer 1

1

You can have two fields in schedule table:

  • operation_type
  • operation_id

Then you can select everything like this:

SELECT s.*, l.weight, d.driver, d.fuel, mo.mechanic, mo.parts
  FROM schedule AS s
  LEFT JOIN loading_operation AS l ON s.operation_id=l.id
                                  AND s.operation_type='l'
  LEFT JOIN delivery_operation AS d ON s.operation_id=d.id
                                  AND s.operation_type='d'
  LEFT JOIN maintenance_operation AS mo ON s.operation_id=mo.id
                                  AND s.operation_type='m';

and the the output of this query would be something like:

id  date_from  date_to    operation_type operation_id weight driver fuel mechanic 
 1 2019-02-06 2019-03-14       m            5                               874  
 2 2019-01-16 2019-02-24       d            3                 431     838         
 3 2019-03-16 2019-03-03       m            5                               874  
 4 2018-12-16 2019-02-11       m            2                               975  
 5 2019-02-12 2019-03-03       d            1                 825     438         
 6 2019-02-12 2019-03-14       d            7                 604     534         
 7 2019-01-11 2019-02-25       l            9          304                       
 8 2019-03-15 2019-02-14       m            8                               188  
 9 2019-03-05 2007-04-20       m            2                               975  
10 2019-02-25 2007-04-22       m            5                               874  
4
  • yes I see what you mean but there would be no one-to-one join, if I want to get the entire schedule with all operations how could I do this in SQL?
    – jim smith
    Mar 16, 2019 at 3:05
  • 1
    How about this? Mar 18, 2019 at 14:53
  • In the end I created one to one ids on the main schedule table and just left join the table and a trigger to prevent more than one column being populated....
    – jim smith
    Mar 19, 2019 at 13:57
  • You got it working? Post your solution as an answer. Mar 19, 2019 at 14:20

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.