Consider if @column for specifying foreign key can be confusing

[ramnivas]

Currently, we use @column to designate both: the column in the current table and the column in the foreign table (inferred based on the cardinality), but this can be confusing. For example, in the snippet below, one may think that the venueid is a column in Venue.

@postgres
service ConcertService {
  type Concert {
    ...
    @column("venue_id") venue: Venue
    @column("fallback_venue_id") fallback: Venue
  }

  type Venue {
    ...
    @column("venue_id") concerts: Set<Concert>?
    @column("fallback_venue_id") fallback_concerts: Set<Concert>?
  }
}

We could introduce @foreign_column to demarcate self columns from those in another table:

@postgres
service ConcertService {
  type Concert {
    ...
    @column("venue_id") venue: Venue
    @column("fallback_venue_id") fallback: Venue
  }

  type Venue {
    ...
    @foreign_column("venue_id") concerts: Set<Concert>?
    @foreign_column("fallback_venue_id") fallbackConcerts: Set<Concert>?
  }
}

Alternatively, we can introduce a @relation annotation to connect fields from two different types:

@postgres
service ConcertService {
  type Concert {
    ...
    @relation(Venue.concerts) venue: Venue
    @relation(Venue.fallbackConcerts) fallback: Venue
  }

  type Venue {
    ...
    @relation(Concert.venue) concerts: Set<Concert>?
    @relation(Concert.fallback) fallbackConcerts: Set<Concert>?
  }
}

Developers can still specify @column to specify the column, but only for columns that actually exist in the surrounding table:

@postgres
service ConcertService {
  type Concert {
    ...
    @relation(Venue.concerts) @column("v_id") venue: Venue // column name explicit
    @relation(Venue.fallbackConcerts) fallback: Venue // column name inferred here
  }

  type Venue {
    ...
    @relation(Concert.venue) concerts: Set<Concert>?
    @relation(Concert.fallback) fallbackConcerts: Set<Concert>?
  }
}