铁路售票系统简易实现表

思路：
实现退改必须要有车次表 、并实时计算当前车次余票 。

CREATE TABLE `tram_line` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) NOT NULL COMMENT '名称',
  `tram_sta` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '起点',
  `tram_ed` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '终点',
  `first_time` varchar(20) DEFAULT NULL COMMENT '首班',
  `last_time` varchar(20) DEFAULT NULL COMMENT '末班',
  `discontinuity_count` int DEFAULT NULL COMMENT '间隔',
  `img` varchar(255) DEFAULT NULL COMMENT '线路图',
  `ticket` int DEFAULT NULL COMMENT '放票数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='线路表';

CREATE TABLE `tram_schedule` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '车次ID',
  `line_id` int NOT NULL COMMENT '线路ID',
  `departure_date` varchar(50) NOT NULL COMMENT '发车日期',
  `departure_time` varchar(50) NOT NULL COMMENT '发车时间',
  `available_ticket` int NOT NULL DEFAULT '0' COMMENT '余票',
  `changed_count` int NOT NULL DEFAULT '0' COMMENT '改签数量',
  `refunded_count` int NOT NULL DEFAULT '0' COMMENT '退票数量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='车次表';

CREATE TABLE `tram_order` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `no` varchar(50) NOT NULL COMMENT '订单编号',
  `uid` int NOT NULL COMMENT '用户',
  `ticket_detail` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '原票',
  `count` int NOT NULL DEFAULT '1' COMMENT '数量',
  `amount` decimal(10,2) NOT NULL COMMENT '金额',
  `status` varchar(20) NOT NULL COMMENT '状态["已支付","申请退款","已退款","申请改签","已改签"]',
  `new_ticket_detail` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '新票',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表';