Ruby Sinatra Datamapper/database confusion

I'm confused as to how database relationship works.

Say I have a Border Crossing('crossing'), which has two Directions('north', 'south'), each of which direction has 2 types of lanes ('normal','fast'), each of which lane has 2 metrics (=data) ('delay','queue_length').

In reality there are several crossings, with more lane types and more metrics.

How the heck should I store that in a database? I've used databases before, but never did table joins or one-to-many or anything like that.

I came across Datamapper and since I'm learning how to us Sinatra I thought I'd give it a go. In the tutorial (http://datamapper.org/getting-started.html), the "one-to-many" part just screamed "this is what you need", so I started fiddling around.

    require 'data_mapper'

    DataMapper.setup(:default, ENV['DATABASE_URL'] || "sqlite3://#{Dir.pwd}/development.db")

      class Crossing
      include DataMapper::Resource

      property :id, Serial
      property :name, String,:unique=>true
      has n, :directions
    end

    class Direction
      include DataMapper::Resource

      property :id, Serial
      property :direction, String,:unique=>true
      belongs_to :crossing
      has n, :lanes
    end

    class Lane
      include DataMapper::Resource

      property :id, Serial
      property :lane, String
      belongs_to :direction
      has n, :datas
    end

    class Data
      include DataMapper::Resource

      property :id, Serial
      property :name, String,:unique=>true
      property :value, String
      belongs_to :lane
    end

    DataMapper.finalize.auto_migrate!

I just thought this looked so elegantly put: "crossing has n directions, directions has n lanes, etc"

Then:

    Crossing.create(:name => "crossing")

    Direction.create(:direction => "north")
    Direction.create(:direction => "south")

    Lane.create(:lane => 'normal')
    Lane.create(:lane => 'fast')

    Data.create(:data => 'delay')
    Data.create(:data => 'queue_length')

    // now how do I retrieve find the data of a lane of a direction of a crossing?

Now, what I will input and retrieve all the time is the Data part. Does this whole thing make sense or I'm just not understanding what table associations are for? I know I could just have a gigantic object instead of this but I'm pretty sure that's a weird way of doing things.

    @crossing = {
      'crossing name' => {

        :directions => {

          :north => {

            :normal => {

              :delay => '10 min',

              :queue => '100 m'
            },

            :fast => {

              :delay => '1 min',

              :queue => '10 m'
            }
          },

          etc etc etc

    }

and then access the data like @crossing[:north][:normal][:delay]....but I kinda feel like a database would be better?

Am I making any sense in any way? Anybody got some pointers for a young grasshoper?

Solution

I would rather go with this structure:

Data belongs to Crossing, Direction and Lane; it has properties for delay and queue
Direction has many Data, and has exactly two rows
Lane has many Data, and has exactly two rows
Crossing has many Data, and has many rows

The reason is, you don't want to repeat the strings "north", "south" etc in your database.

Then, first seed the database with constant tables:

Direction.create(direction: 'north')
Direction.create(direction: 'south')

Lane.create(lane: 'normal')
Lane.create(lane: 'fast')

Then you can make your crossings:

cool_crossing = Crossing.create(name: 'My Cool Crossing')
not_cool_crossing = Lane.create(name: 'My Not So Cool Crossing')

and add data points:

north = Direction.first(name: "north")
normal = Lane.first(name: "normal")

Data.create(
  crossing: cool_crossing,
  lane: normal,
  direction: north,
  delay: 10,
  queue: 1
)

and retrieve data by:

all_data_for_cool_crossing = Data.all(
  crossing: cool_crossing
)

data_for_cool_crossing_normal_north = Data.first(
  crossing: cool_crossing,
  lane: normal,
  direction: north
)