-- lookup tables
-- this will be populated once by hand and doesn't need an id field
create table Country(
   name              varchar(40),
   primary key ( name )
)

-- this could be populated by hand or an admin screen
-- Dorset in the UK
create table Location(
   id                 int,
   name               varchar(40),
   country            varchar(40),
   primary key ( id )
)

-- this will be populated once by hand
-- ie Deep Sea, River etc
create table FishingType(
    fishingType         varchar(20),
   primary key ( fishingType )
)

-- this could be populated by hand or an admin screen
-- ie Trout, Salmon etc
create table Fish(
    name            varchar(40),
    extraInfo       varchar(255),
    -- season field ?
   primary key ( name )
)

-- this could be populated by hand or an admin screen
-- ie Resteraunt, French Resteraunt, B&B, Tackle shop, Garage
create table AmenityType(
    amenityType             varchar(40),
    amenityParentType        varchar(40),
   primary key ( amenityType )
)

-- this will require a screen for users to add new amenities
-- you may want an approval field for you to allow the entry to be seen by the public
create table Amenity(
    id                int,
    name              varchar(100),
    amenityType       varchar(40),
    tel               varchar(20),
    address            varchar(100),
    zip               varchar(12),
   primary key ( id )
)


-- this will require a screen for users to add new amenities
-- you may want an approval field for you to allow the entry
create table Resort(
   id             int,
   name           varchar(40)
   locId          int,
   primary key ( id )
)


create table ResortFishingType(
    resortId             int,
    fishingType          varchar(20),
   primary key ( resortId, fishingType )
)

create table ResortFish(
    resortId             int,
    fishName             varchar(40),
   primary key ( resortId, fishName )
)

create table ResortAmenity(
    resortId             int,
    amenityId            int,
   primary key ( resortId, amenityId )
)


-- any user can supply feedback once on a resort
-- this is open to abuse though
create table ResortFeedback(
   ip                  varchar(20),
    resortId           int,
   feedbackDate       datetime,
   feedbackTxt         varchar(255),
   score                int,
   primary key ( ip, resortId )
)

-- any user can supply feedback once on an amenity
-- this is open to abuse though
create table AmenityFeedback(
   ip                      varchar(20),
    amenityId              int,
   feedbackDate            datetime,
   feedbackTxt             varchar(255),
   score                   int,
   primary key ( ip, amenityId )
)