Ms-access – Local bus schedule structure/design

It is very difficult to accurately suggest a solution with no ability to run testing, or see how the DB is indexed etc. But I'm going to try anyway.

You ideally need to find a balance, if your query is likely to return a lot of data and run quickly then I would err towards running the query on the main table twice, if it is likely to take a long time and return a relatively small number of rows then I would stick with the temp table approach.

Given the information you have provided in the question it appears as though there is no problem with the speed of the select, in which case I would tend to agree with you, the additional cost of inserting into a temp table, then selecting from the temp table is as much overhead as performing the select query twice. The queries can be simplified as follows:

SELECT  COUNT(*) [TotalHolidaysCount],
        COUNT(DISTINCT PropertyID) [PropertyCount]
FROM    dbo.Holiday
WHERE   DepartureDate > GETDATE() 
AND     Country = 'FR'


SELECT  *
FROM    (   SELECT  *, ROW_NUMBER() OVER (ORDER BY(CASE WHEN StartDate <= '01/Apr/2013' THEN 1 ELSE 0 END) ASC, [Price] ASC) [RowNumber]
            FROM    (   SELECT  h.*, ROW_NUMBER(PARTITION BY PropertyID, ORDER BY Price ASC) [PropertyRow]
                        FROM    dbo.Holiday
                        WHERE   DepartureDate > GETDATE() 
                        AND     Country = 'FR'
                    ) h
            WHERE   PropertyRow = 1
        ) h
WHERE   Rownumber BETWEEN 1 AND 10

If you do find this is a performance hit and you would rather cache the results of the select I'd be inclined to use table variables instead of temp tables, and only store the Holiday ID primary key, and join back to holiday on the fast indexed join of primary key = primary key as follows:

DECLARE @Results TABLE (ID INT NOT NULL PRIMARY KEY)
INSERT @Results
SELECT  ID
FROM    dbo.Holiday
WHERE   DepartureDate > GETDATE() 
AND     Country = 'FR'

SELECT  COUNT(*) [TotalHolidaysCount],
        COUNT(DISTINCT PropertyID) [PropertyCount]
FROM    dbo.Holiday h
        INNER JOIN @Results r
            ON r.ID = h.ID

SELECT  *
FROM    (   SELECT  *, ROW_NUMBER() OVER (ORDER BY(CASE WHEN StartDate <= '01/Apr/2013' THEN 1 ELSE 0 END) ASC, [Price] ASC) [RowNumber]
            FROM    (   SELECT  h.*, ROW_NUMBER(PARTITION BY PropertyID, ORDER BY Price ASC) [PropertyRow]
                        FROM    dbo.Holiday h
                                INNER JOIN @Results r
                                    ON r.ID = h.ID
                    ) h
            WHERE   PropertyRow = 1
        ) h
WHERE   Rownumber BETWEEN 1 AND 10

This way you are caching as little data as possible (one column of integers), while still retaining enough data to do a fast indexed search on dbo.Holiday.

It ultimately has to come down to looking at your execution plans, creating proper indexes and testing various approaches to find the one that best suits you.

I think you need a track_segment table to track the segments between stations. The problem with your Station table is that you include distance there, but distance to/from what?

So let's try this:

Train
-----
  ID
  Train_number
  Days_of_operation
  other_details

Station
-------
  ID
  Name
  other_data

Track_Segment
-------------
  From_Station_ID
  To_Station_ID
  Length
  Line_ID

Line
----
  ID
  Name
  other_data

Train_Run
---------
  ID
  Train_ID
  from_Station_ID
  to_station_ID
  Depart_datetime  
  Arrive_datetime
  other_details

This allows a Train_run to reference a specific train, as well as the two endpoints of the journey. Of course, there might be multiple segments of track between these two stations, so the total length of the journey could be had by looking at all the segments between from_station_id and to_station_id in track_segment. I also included the line table, since you had a refernce to it in your tables. I assume that a "Line" is just a descriptive term for a specific train journey that might have multiple stops along the way, like "Orient Express" or "Rocky Mountain Route".