drop database if exists attraction;
create database attraction;
use attraction;

create table transit (
  transit_id int primary key,
  name varchar(32) not null,
  -- May need more. Got these from
  -- https://developers.google.com/places/web-service/supported_types
  type enum ('airport', 'bus_station', 'subway_station',
             'taxi_stand', 'train_station', 'transit_station'),
  -- In case you want to retrieve ratings, photos, etc. from Google
  google_place_id char(50)
);

create table attraction (
  attraction_id int primary key,
  name varchar(32) not null,
  description varchar(256) not null,

  -- address
  number int,
  street varchar(32) not null,
  postcode varchar(16) not null,

  price decimal(5,2), -- NULL means free
  transit_id int not null, -- nearest transit station

  foreign key (transit_id) references transit(transit_id)
);
