re:Data modeling issues for a travel planning system

I didn't consider your logic very carefully, quick suggestions:

• Use an integer as the primary key for each table. Integer is more efficient and much easier to maintain. The integer can be auto increment, or use a table to maintain last used ID.
  • One PK in one table.

The tables will look like:
STATION:
- (PK) ID (interger)

• statname (the name of the station)
• interchange (Y = interchange station, N = non-interchange station)

LINE:
- (PK) ID (integer)

• linename (the name of the line)

STATION_LINE:
- (PK) ID (integer)

• line_id
• statname (station name)
• direction_id
• nextstat_id

ATTRACTION:
- (PK) ID (integer)

• attrname (attraction name)
• (FK) nearstat_id (the station which is nearest to the attraction)
• distance (the distance between the attraction and its nearest station) ....

Hope this helps. Received on Fri Nov 04 2005 - 02:10:01 CST