Basic database relations

Question

I recently opened a travel agency and I need a database but don't have the money yet to have it done by a professional...

A very basic thing will do until I can have it done better.

We do "Transfer" services from airport to hotel and "Tours" with everything arranged by us.

So far I guess I need Customers, Transfer, Tour Operation and Booking tables.

1) Customers will have the usual personal information

2) Transfer will contain info such as time, flight number, airport and stuff like that.

3) Tour Operations refers to each and every single activity we arrange for this tour and it looks like date/city/description (train to, train from, guide, entrance fee, taxi) / sales price / net price / profit / supplier (for example who's providing the guide, or what company is providing the taxi service)

4) Booking will be the main table and will contain

Booking No. | Date | Customer Name (Linked from the Customers table) | Type (Transfer or Tour) | Confirmed (Yes/No)| Invoice Sent (Yes/No)| Payment Received (Yes/No)

I have no idea how to link these tables. 1 to many or many to whatever I tried looking up some stuff but I don't even know the basics and need someone's help to at least get started and get the hang of it.

Please help me if it's not too much.

Thank you!

Answer 1

Here's one example of what you could do:

Customer:

Where all the customers are going to be.

• id_customer (Autoincrement): Customer Unique id.
• desc_customer: Customer name.
• status: If you want to put different status like 1 - active, 2 - suspended, 3 - canceled, etc.

I joined the Tour and Transfer in one table called: Operation.

Operation:

There you're going to save all your operations with its customer.

• id_operation (Autoincrement): Operation unique id.
• id_customer: Customer unique id. -- Table Customer
• desc_operation: Operation description.
• otype: Operation type. -- Table BookType.
• odate: Operation date.

OperationType:

In this table you're going to added all your kind of operations like Transfers and Tours.

Booking:

Where all the payments, trackings and confirmations its going to be saved. (The main table).

• id_book (Autoincrement): Book's unique id.
• id_operation: Operation's unique id -- Table Operation.
• bdate: Book date.
• btype: Book type -- Table BookType.
• confirmed: Book's confirmation.
• invoice_sent: Invoice confirmation.
• payment_sent: Payment confirmation.

BookType:

This is helpful if you want to keep different types of booking.

DDLs:

CREATE TABLE IF NOT EXISTS `mydb`.`Customer` (
  `id_customer` INT NOT NULL,
  `desc_customer` VARCHAR(45) NULL,
  `status` INT NULL,
  PRIMARY KEY (`id_customer`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Operation` (
  `id_operation` INT NOT NULL,
  `id_customer` INT NULL,
  `desc_operation` VARCHAR(45) NULL,
  `otype` INT NULL,
  `odate` DATETIME NULL,
  PRIMARY KEY (`id_operation`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`OperationType` (
  `id_otype` INT NOT NULL,
  `desc_type` VARCHAR(75) NULL,
  `active` BIT(1) NULL,
  PRIMARY KEY (`id_otype`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Booking` (
  `id_book` INT NOT NULL,
  `id_operation` INT NULL,
  `bdate` DATETIME NULL,
  `btype` INT NULL,
  `confirmed` BIT(1) NULL,
  `invoice_sent` BIT(1) NULL,
  `payment_sent` BIT(1) NULL,
  PRIMARY KEY (`id_book`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`BookType` (
  `id_btype` INT NOT NULL,
  `desc_btype` VARCHAR(45) NULL,
  PRIMARY KEY (`id_btype`))
ENGINE = InnoDB;

Those DDLs and EER Design were made on MySQL Workbench.