re:Data modeling issues for a travel planning system
From: kevin <kevin.pang_at_gmail-dot-com.no-spam.invalid>
Date: Sat, 05 Nov 2005 02:10:29 -0600
Message-ID: <s6idnTMAUJ9o9fHeRVn_vA_at_giganews.com>
Date: Sat, 05 Nov 2005 02:10:29 -0600
Message-ID: <s6idnTMAUJ9o9fHeRVn_vA_at_giganews.com>
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 - 09:10:29 CET