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