CREATE TABLE Customer -- You for example
(
    ID                      INT                     IDENTITY(1,1)                   NOT NULL,
    FirstName               VARCHAR(32)                                             NOT NULL,
    LastName                VARCHAR(32)                                             NOT NULL,
    [Address]               VARCHAR(128)                                            NOT NULL,
    Email                   VARCHAR(32)                                             NOT NULL,
    PhoneNo                 VARCHAR(32)                                             NOT NULL,
    CONSTRAINT PK_Customer PRIMARY KEY (ID ASC)
)

CREATE TABLE [Port] -- The port that people can go to
(
    ID                      INT                     IDENTITY(1,1)                   NOT NULL,
    [Name]                  VARCHAR(32)                                             NOT NULL, -- The name of the port
    [Location]              VARCHAR(32)                                             NOT NULL, -- Where the port is located
    Capacity                INT                                                     NOT NULL, -- How many Yachts can fit in this port
    CONSTRAINT PK_Port PRIMARY KEY (ID ASC)
)

CREATE TABLE Yacht -- The boat that people can get on
(
    ID                      INT                     IDENTITY(1,1)                   NOT NULL,
    [Name]                  VARCHAR(32)                                             NOT NULL, -- The name of the boat... U.S.S. Minnow!
    Capacity                INT                                                     NOT NULL, -- How many people can fit on the boat
    CONSTRAINT PK_Yacht PRIMARY KEY (ID ASC)
)

CREATE TABLE Charter -- When the boat gets to the port and for how many days
(
    ID                      INT                     IDENTITY(1,1)                   NOT NULL,
    PortID                  INT                                                     NOT NULL, -- Which port they are going to
    YachtID                 INT                                                     NOT NULL, -- Which yacht they are riding on
    ArrivalDate             DATETIME                                                NOT NULL, -- When they arrive
    DurationInDays          INT                                                     NOT NULL, -- How many days they are staying
    CONSTRAINT PK_Customer PRIMARY KEY (ID ASC),
    CONSTRAINT FK_Charter_Port FOREIGN KEY (PortID) REFERENCES [Port] (ID),
    CONSTRAINT FK_Charter_Yacht FOREIGN KEY (YachtID) REFERENCES Yacht (ID),
)

CREATE TABLE Booking -- An order placed by a customer for them and/or their family
(
    ID                      INT                     IDENTITY(1,1)                   NOT NULL,
    CharterID               INT                                                     NOT NULL, -- Port/Yacht/Timeframe association
    TotalCost               MONEY                                                   NOT NULL, -- How much the customer paid for them and/or their family to travel
    StartDate               DATETIME                                                NOT NULL, -- The date that their trip starts
    TotalDays               INT                                                     NOT NULL, -- How many days in duration their trip is
    CONSTRAINT PK_Booking PRIMARY KEY (ID),
    CONSTRAINT FK_Booking_CharterID FOREIGN KEY (CharterID) REFERENCES Charter (ID)
)

CREATE TABLE BookingCustomer
(
    ID                      INT                     IDENTITY(1,1)                   NOT NULL,
    BookingID               INT                                                     NOT NULL, -- Reference to the customer order
    CustomerID              INT                                                     NOT NULL, -- Reference to the customer associated with the order
    CONSTRAINT PK_BookingCustomer PRIMARY KEY (ID),
    CONSTRAINT FK_BookingCustomer_Booking FOREIGN KEY (BookingID) REFERENCES Booking (ID),
    CONSTRAINT FK_BookingCustomer_Customer FOREIGN KEY (CustomerID) REFERENCES Customer (ID)
)
GO