re:Data modeling issues for a travel planning system

In STATION_LINE table, although three fields make one unique record, I
still prefer using an unique ID as the primary key, and control the
logic in the program.

The definitions are not very reasonable IMHO, direction_id should be
placed in the line and doesn't have to be repeated in every record of
STATION_LINE. It's better not to use nextstat_id if there are not many
stations in one line, it would be very inconvenient to navigate.

How about this:

LINE:
- (PK) ID (integer)

linename (the name of the line)

start_station_id

end_station_id

STATION_LINE:
- (PK) ID (integer)

line_id

statname (station name, this is also in STATION table, maybe this
one should be removed)

order_number (an integer to specify station's order in the line,
the order number is relative, the smallest is the first station...)

For example: to list all stations of a line in order:
select id from STATION_LINE order by order_number.
Received on Sat Nov 05 2005 - 02:10:29 CST