火车票站站查询 请各位大神看看数据库表结构设计是否合理？

[木木木大叔]

车次表
id，车次编号，始发站，终点站，车型Id
车型表
id，车型编码，车型名称
列车时刻表
id，车次编号，到站时间，发车时间，车站，顺序
行程价格表
id，出发车站，目的车站，车型编码，成人价格，儿童价格
车站表
id，车站编码，车站名称
车次表班期表
id，车次Id，行程Id，出发日期


下面是建表语句
CREATE TABLE [dbo].[carClass](
[id] [int] IDENTITY(1,1) NOT NULL,
[ccCode] [char](10) COLLATE Chinese_PRC_CI_AS NULL,
[ccName] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_carClass] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[route](
[id] [int] IDENTITY(1,1) NOT NULL,
[startStationCode] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[reachStationCode] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[adultPrice] [money] NULL,
[childPrice] [money] NULL,
[direction] [int] NULL,
[carClassId] [int] NULL,
CONSTRAINT [PK_route] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[station](
[id] [int] IDENTITY(1,1) NOT NULL,
[stationCode] [char](10) COLLATE Chinese_PRC_CI_AS NULL,
[stationName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[ifUse] [int] NULL,
CONSTRAINT [PK_station] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[timeInfo](
[id] [int] IDENTITY(1,1) NOT NULL,
[sort] [int] NULL,
[depTime] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[arrTime] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[trainNumber] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[stationCode] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[ifUse] [int] NULL,
CONSTRAINT [PK_timeInfo] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[train_route](
[id] [int] IDENTITY(1,1) NOT NULL,
[trainNumber] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[routeId] [int] NULL,
[startDate] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[ifUse] [int] NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[trainInfo](
[id] [int] IDENTITY(1,1) NOT NULL,
[trainNumber] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[reachStationCode] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[startStationCode] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[carClassId] [int] NULL,
[ifUse] [int] NULL,
CONSTRAINT [PK_trainInfo] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

[风一样的执着]

站点之间的里程需要吗

[木木木大叔]

引用 4 楼 tuwei123 的回复:

站点之间的里程需要吗

不需要

[聪头]

涉及到价格，应该还有座位等级吧。另外，发车日期，应该有多种形式，如每周几、每月几日、固定日期。

[木木木大叔]

引用 2 楼 anchor1 的回复:

涉及到价格，应该还有座位等级吧。另外，发车日期，应该有多种形式，如每周几、每月几日、固定日期。

座位等级涉及不到，只考虑普通列车，发车日期的话，暂时也不需要考虑那么复杂

[liangtu]

应该有个字段把该线路包含的全部车站都加进来，方便做站站查询。