[SQL] Sql Server: Creating a table with multiple foreign keys

I have to create a set of tables in SQL Server.

Those are:

BusCity: Traveling Cities
BusType: AC/Non AC
BusInformation: Information about Name, Traveling City, Bus Fare,etc (Add by the Admin)
BookingDetails: Storing Booking details after customer booked the travel.
BookedStatus: Availability of Bus.

I have a problem in creating these tables.

Following is my DDL code:

Create Table Customer
(
CustomerId int primary key identity(100,1),
CustomerName varchar(100) not null,
CustomerEmail varchar(100) not null,
CustomerPassword varchar(50) not null,
CustomerPhone int not null,
)

Create Table BusCity
(
BusCityId varchar(10) primary key,
BusCityName varchar(100) not null unique
)

Create Table BusType
(
BusTypeId int identity(10,1) primary key,
BusTypes varchar(100) not null unique
)

Create Table BusInformation
(
BusInformationId varchar(10) primary key,
BusName varchar(100) not null unique,
BusNumOfSeats int not null,
BusFromCity varchar(10) not null,
BusToCity varchar(10) not null,
BusTravelPrice int not null,
BusTypeId int,
constraint Fk_BusInfo_BusType Foreign key(BusTypeId) references BusType(BusTypeId),
constraint Fk_BusInfo_BusFromCity Foreign key(BusFromCity) references BusCity(BusCityId),
constraint Fk_BusInfo_BusToCity Foreign key(BusToCity) references BusCity(BusCityId)
)

Create Table BookingDetails
(
BookingDetailsId int identity(2000,1) primary key,
CustomerId int,
BusInformationId varchar(10),
BookingDateofTravel Date,
BookingFromCity varchar(10) not null,
BookingToCity varchar(10) not null,
BookingNumOfSeats int not null,
BookingTravelPrice int not null,
constraint Fk_BookingDetails_BusInformation Foreign key(BusInformationId)references BusInformation(BusInformationId),
constraint Fk_BookingDetails_Customer Foreign key(CustomerId)references Customer(CustomerId),
constraint Fk_BusDetails_BusFromCity Foreign key(BookingFromCity) references BusCity(BusCityId),
constraint Fk_BusDetails_BusToCity Foreign key(BookingToCity) references BusCity(BusCityId)
)

Create Table BookedStatus
(
BookedStatusId int identity(3000,1) primary key,
AvailableSeats int not null,
BookedDate Date,
IsAvailable bit,
BusInformationId varchar(10),
constraint Fk_BookedStatus_BusInfo Foreign key(BusInformationId)references BusInformation(BusInformationId)
)


My issue is,
1.whether I mapped the table relationship properly between the tables.
2.Included multiple foreign key columns in a table(BusInformation, BookingDetails).

Please help me on the same.

Online Bus Ticket Booking

This project has two user roles - First is Admin and second are Customers. The project allows customers to book their travel tickets online.

Administrator

Administrator user of the website can add city information, bus information, bus routes (From City to To City), Bus details(bus information/Bus ticket price per city to city details), No of Seats per bus, Available seats per bus on booking date, Bus Available and Booked status.

Customer

Customers can search available buses for booking tickets by Bus Name/ Traveling City From and To / Date of traveling with one way or return and by price.

• 
  There needs to be a search feature that will list all available buses and bus details.
  
  
• 
  For search and details, customers don't need to be logged in. Any user can search and check for available bus and their routes.
  
  
• 
  But for booking, Customer needs to be logged in, Customer can register to the web application for booking tickets .
  
  
• 
  After a customer is logged in, he or she can book a Bus Ticket by entering number of seats they need, date of travel, and From City
  and To City.
  
  
• 
  No need for payment details for now.
  
  
• 
  Customer can register and book tickets. Admin can view all booked information.
  

Continue reading...