Database design, working but looking for better ways

Rails 3.1.3

Hi. I have designed the database used for my travel plan application.
You should be able to look at the diagram in the link below, hopefully.

https://cacoo.com/diagrams/biDSyxh8yzk2kIeg

("belongs_to" is omitted since it's obvious)

Basically, the application can list the destinations from a choice of
the departure.
Not mention that the departure_id (integer) and destination_id (integer)
should not appear but the "city.name" must appear for the users.

Each "Plan" will find both Departure and Destination implicitly and the
only corresponding City.name 's must show up on the screen.

In order to save the database space, I detach the city name (string)
from both departures and destinations, dealing with the connections
among them by id's.

It works. But I am wondering if there are better ways to do it. The
logic becomes rather complex and inevitably the database workload will
be troublesome(?), I suppose.

So what do you think?

soichi

Rails 3.1.3

Hi. I have designed the database used for my travel plan application.
You should be able to look at the diagram in the link below, hopefully.

https://cacoo.com/diagrams/biDSyxh8yzk2kIeg

("belongs_to" is omitted since it's obvious)

Basically, the application can list the destinations from a choice of
the departure.
Not mention that the departure_id (integer) and destination_id (integer)
should not appear but the "city.name" must appear for the users.

Each "Plan" will find both Departure and Destination implicitly and the
only corresponding City.name 's must show up on the screen.

In order to save the database space, I detach the city name (string)
from both departures and destinations, dealing with the connections
among them by id's.

It works. But I am wondering if there are better ways to do it. The
logic becomes rather complex and inevitably the database workload will
be troublesome(?), I suppose.

Destination and Departure should not be separate tables, you should
just have a table of places, then city is just a member of Place and a
FlightName belongs to two Places, as departure and destination.

I am not sure why a plan needs destination and departure, can it not
get these from the FlightName?

Colin

Destination and Departure should not be separate tables, you should
just have a table of places, then city is just a member of Place and a
FlightName belongs to two Places, as departure and destination.

Say, flight A is

Tokyo -> NewYork

and flight B is

NewYork -> Tokyo

Wouldn't that be better to separate the places from the flight plan
because four string values are stored?
Whereas if the place is separate

flight A is

place_id:1 -> place_id:2

flight B is

place_id:2 -> place_id:1

dealing with integers and only two string values are necessary.
There are thousands of flights that are to be stored in the database.

I am not sure why a plan needs destination and departure, can it not
get these from the FlightName?

You are right...I am stupid :wink:

soichi

Destination and Departure should not be separate tables, you should
just have a table of places, then city is just a member of Place and a
FlightName belongs to two Places, as departure and destination.

Say, flight A is

Tokyo -> NewYork

and flight B is

NewYork -> Tokyo

Wouldn't that be better to separate the places from the flight plan
because four string values are stored?
Whereas if the place is separate

Sorry, I was not clear, What I meant was that there should be a places
table, not separate departures and arrivals tables as you show in your
diagram.

Colin

Sorry, I was not clear, What I meant was that there should be a places
table, not separate departures and arrivals tables as you show in your
diagram.

Oh, thanks anyway. I appreciate it!

soichi