Optimal Table Structure

Question

We have a parent table, Journal and a child table, Stops.

A Journal can have X amount of Stops.

For a good 80% of our SQL queries, our customers are just interested in the first and last Stop for each Journal.

We often have queries that do something like this:

SELECT
      MINStop.PhysicalCity AS MinStopCity,
      MAXStop.PhysicalCity AS MaxStopCity,
      MINMax.DocNo AS JournalDocNo
FROM (        
    SELECT
        MIN(Stops.StopsPkey) AS MINStopPkey,
        MAX(Stops.StopsPkey) AS MAXStopPkey,
        Stops.JournalPkey,
        Journal.DocNo
    FROM Journal
    LEFT OUTER JOIN Stops ON Journal.JournalPkey=Stops.JournalPkey
    WHERE Journal.Type=150  
    GROUP BY Stops.JournalPkey, Journal.DocNo) MINMax
LEFT OUTER JOIN Stops MINStop ON MINStop.StopsPkey=MINMax.MINStopPkey
LEFT OUTER JOIN Stops MAXStop ON MAXStop.StopsPkey=MINMax.MAXStopPkey

It works fine, but definitely suffers at the larger locations.

It's just how it's always been done here, and I wanted to reach out for possible improvements. Would it be worth it to store the first and last Stop data in the Journal table, even if that data is duplicated?

We use Advantage Database 11.x so are a bit limited on the SQL features. Probably our only route would be through table modifications.

I had a quick look in the docs, and you are a bit limited. Do you have indexes on stops (journalpkey, stopspkey)? You may also want to ask the question in http://devzone.advantagedatabase.com/forum — Lennart - Slava Ukraini, Dec 23 '19 at 23:53
We do yes. Unfortunately advantage is in a code freeze, and their support is just not what it once was. I was hoping for some general advice that might be able to apply to our use case. — FLDelphi, Dec 24 '19 at 03:12
Do you update and delete in stops, or do you just insert there? — Lennart - Slava Ukraini, Dec 24 '19 at 12:36
Ok, I asked because an IES (Incremental Evaluation System) is trivial for insert only. A bit more complicated for update, delete. The idea is to use a new table that keep track of min, max. The new table can be maintained with triggers. There is of course a prize to pay during modification, but lookup becomes very fast. — Lennart - Slava Ukraini, Dec 24 '19 at 15:54
Would the table be two rows, or one row with the fields duplicated. For example FirstStopCity & LastStopCity. — FLDelphi, Dec 24 '19 at 16:48
Instead of selecting min(...), max(...) you select min, max from the new table — Lennart - Slava Ukraini, Dec 24 '19 at 17:28

score 0 · Answer 1 · answered Dec 24 '19 at 21:32

Here is a sketch for an Incremental Evaluation System (IES):

CREATE TABLE stops_min_max
( JournalPkey ... not null primary key
, MINStopPkey ... not null
, MAXStopPkey ... not null
);

CREATE TRIGGER ...
AFTER INSERT ON stops
BEGIN
        MERGE INTO stops_mean_max x
        USING (
            VALUES (__new.JournalPkey, __new.StopPkey, __new.StopPkey)
        ) y (JournalPkey, MINStopPkey, MAXStopPkey)
                ON x.JournalPkey = y.JournalPkey
        WHEN MATCHED THEN
            UPDATE SET (MINStopPkey, MAXStopPkey) 
                     = (LEAST(x.MINStopPkey, y.MINStopPkey)
                       ,GREATEST(x.MAXStopPkey, y.MAXStopPkey))
        WHEN NOT MATCHED THEN
            INSERT (JournalPkey, MINStopPkey, MAXStopPkey)
            VALUES (y.JournalPkey, y.MINStopPkey, y.MAXStopPkey);

Triggers for update and delete should be added

Instead of looking for min, max in stops, they can be found in stops_min_max

Since I have no idea what your tables look like, and I don't have access to the DBMS you are using, a sketch is the best I can do.

Optimal Table Structure

1 Answers1