Sql Server：创建一个包含多个外键的表

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

我必须在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.

BusCity:旅游城市巴士类型:AC /非AC巴士信息:有关姓名,旅游城市,公共汽车票价等的信息(由管理员添加)预订详细信息:在客户预订旅行后存储预订详细信息。 BookedStatus:总线的可用性。

I have a problem in creating these tables.

我在创建这些表时遇到问题。

Following is my DDL code:

以下是我的DDL代码:

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).

我的问题是,1。我是否在表之间正确映射表关系。 2.在表中包含多个外键列(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.

该项目有两个用户角色 - 第一个是Admin,第二个是Customers。该项目允许客户在线预订旅行机票。

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 .

但是对于预订,客户需要登录,客户可以注册到Web应用程序以预订机票。
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.

客户可以注册和预订门票。管理员可以查看所有预订信息。

1 个解决方案

#1

I'm not sure you've given enough info to answer 1) - we don't know enough about the inter dependencies between your data from the statements given to comment on all the possible relationships

我不确定你是否给出了足够的信息来回答1) - 我们对于所有可能关系的评论中的数据之间的相互依赖关系不够了解

Foreign keys can be created when the column is specified in the create table:

在create table中指定列时,可以创建外键:

CREATE TABLE BusBlah (
  BusID int PRIMARY KEY,
  StatusID int not null REFERENCES StatusesTable(StatusIDColumnInStatusesTable)
  ...
)

Or they can be added after with an ALTER (this is the way SQL Management Studio does it)

或者可以在使用ALTER后添加它们(这是SQL Management Studio执行此操作的方式)

ALTER TABLE BusBlah  
ADD CONSTRAINT SomeConstraintName FOREIGN KEY (StatusID)  
REFERENCES StatusesTable (StatusIDColumnToReference) ;

Ultimately, if you want a perfect set of scripts, you can always design your tables visually, including relationships, in SQLServer management studio, and then ask it to script them and take a look at the results. If your query is in relation to an academic exercise, maybe don't hand in the scripts that SSMS made, as it'll be obvious they were machine prepped, - but you can use them as a base to check what you're doing

最终,如果您需要一组完美的脚本,您可以在SQLServer管理工作室中直观地设计表格,包括关系,然后让它们编写脚本并查看结果。如果你的查询与学术练习有关,也许不要交出SSMS制作的脚本,因为很明显他们是机器准备的,但你可以用它们作为基础来检查你在做什么

If it's not an academic exercise, I generally think that designing these things visually in SSMS is a better and guided way to do it. You can always script them as above if you're putting them into a database migration or other mechanism that sets up a new db from scratch programmatically

如果这不是学术练习,我通常认为在SSMS中以视觉方式设计这些东西是一种更好,更有指导性的方法。如果您将它们放入数据库迁移或其他以编程方式从头开始设置新数据库的机制,您始终可以按上述方式编写脚本

#1