Mysql – schedule join multiple tables

database-designmysql-5.7

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?

Best Answer

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

Related Solutions

How to model schedule and keep good constraints

I think this can be done with an additional column (say Day) that will be allowed to hold only 2 possible values (1 and 2).

Assuming a team cannot be in 2 or more stores in the same date, then you could have a UNIQUE constraint on (Team, Date) and another on (Cycle, Team, Store, Day).

The constraints would be:

UNIQUE CONSTRAINT uq_1
  (Team, Date),
UNIQUE CONSTRAINT uq_2
  (Cycle, Team, Store, Day),
CHECK CONSTRAINT ck_max_2_days_per_team_store_cycle
  (Day in (1,2))

and the data:

Cycle Team Store Date   Day
1     1     1    1-Dec   1
1     1     1    2-Dec   2
1     1     2    3-Dec   1
1     1     2    4-Dec   2
1     1     3    8-Dec   1
1     1     3    9-Dec   2
1     1     4   10-Dec   1
1     1     4   11-Dec   2
1     2    10    1-Dec   1
1     2    10    2-Dec   2
1     2    11    3-Dec   1
1     2    11    4-Dec   2
1     2    12    8-Dec   1
1     2    12    9-Dec   2
1     2    13   10-Dec   1
1     2    13   11-Dec   2

Mysql – Uber-wide tables: Time to switch to Mongo

1) Consider how this information will be used. Will the owner want to try lots of ad hoc analysis and data exploration? Does your potential data store have tools or connection libraries that allow this? Will the user have to resort to writing Map and Reduce functions for every question posed?

2) Do you mean that the same question is on different questionaires and you're wondering if you should recognise they're the same in your data store design? Yes, absolutely you should, whatever thechnology you end up with.

3) The DBMSs have column limits, too. They may be larger, but they're not infinite.

4) Store your data as one row per answer. There are several qustions here on quiz design. Here's one picked at random. These will give you pointers and any detailed questions remaining will be good material for future DBA.SE posts.

Best Answer

Related Solutions

How to model schedule and keep good constraints

Mysql – Uber-wide tables: Time to switch to Mongo

Related Question