租房水电缴费管理MYSQL设计

CREATE TABLE `admin`  (
  `admin_id` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '管理员编号',
  `admin_loginname` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '管理员登录用户名',
  `admin_password` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '管理员登录密码',
  `admin_username` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '管理员姓名',
  PRIMARY KEY (`admin_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `power`  (
  `power_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电表编号',
  `userid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户编号',
  `power_count` decimal(9, 2) NOT NULL COMMENT '电表跑数',
  `power_time` date NOT NULL COMMENT '电表时间',
  `power_status` tinyint(1) NOT NULL COMMENT '电费缴费状态',
  `price_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电费价格编号',
  `power_beforecount` decimal(9, 2) NOT NULL COMMENT '上月电表跑数',
  PRIMARY KEY (`power_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `price`  (
  `price_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '价格编号',
  `price_time` date NOT NULL COMMENT '价格日期',
  `isactive` tinyint(1) NOT NULL COMMENT '价格状态',
  `waterprice` decimal(4, 2) NOT NULL COMMENT '水费价格',
  `powerprice` decimal(4, 2) NOT NULL COMMENT '电费价格',
  PRIMARY KEY (`price_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `rent`  (
  `id` int(10) NOT NULL,
  `admin_id` varchar(3) NOT NULL COMMENT '管理员编号',
  `user_id` varchar(10) NOT NULL COMMENT '用户编号',
  `water_id` varchar(10) NOT NULL COMMENT '消费编号',
  `price_id` varchar(10) NOT NULL COMMENT '价格编号',
  `power_id` varchar(10) NOT NULL COMMENT '电费编号',
  `create_time` date NOT NULL DEFAULT now() COMMENT '添加时间',
  PRIMARY KEY (`admin_id`, `user_id`, `water_id`, `price_id`, `power_id`, `id`)
);

CREATE TABLE `users`  (
  `user_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户编号',
  `user_loginname` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户登录名',
  `user_password` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户登录密码',
  `user_username` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名',
  `user_address` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户地址',
  `user_phone` int(11) NOT NULL COMMENT '用户电话',
  `price_id` varchar(10) NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `water`  (
  `water_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '水表编号',
  `userid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户编号',
  `water_count` decimal(9, 2) NOT NULL COMMENT '水表跑数',
  `water_time` date NOT NULL COMMENT '水表时间',
  `water_status` decimal(1, 0) NOT NULL COMMENT '水费缴费状态',
  `price_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '水费价格编号',
  `water_beforecount` decimal(9, 2) NOT NULL COMMENT '上月水表跑数',
  PRIMARY KEY (`water_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

ALTER TABLE `rent`ADD INDEX(`admin_id`);
ALTER TABLE `rent`ADD INDEX(`power_id`);
ALTER TABLE `rent`ADD INDEX(`price_id`);
ALTER TABLE `rent`ADD INDEX(`user_id`);
ALTER TABLE `rent`ADD INDEX(`water_id`); //防止出现：ERROR 1005: Can't create table (errno: 150)

ALTER TABLE `admin` ADD CONSTRAINT `fk_admin_rent_1` FOREIGN KEY (`admin_id`) REFERENCES `rent` (`admin_id`);
ALTER TABLE `power` ADD CONSTRAINT `fk_power_rent_1` FOREIGN KEY (`power_id`) REFERENCES `rent` (`power_id`);
ALTER TABLE `price` ADD CONSTRAINT `fk_price_rent_1` FOREIGN KEY (`price_id`) REFERENCES `rent` (`price_id`);
ALTER TABLE `users` ADD CONSTRAINT `fk_users_rent_1` FOREIGN KEY (`user_id`) REFERENCES `rent` (`user_id`);
ALTER TABLE `water` ADD CONSTRAINT `fk_water_rent_1` FOREIGN KEY (`water_id`) REFERENCES `rent` (`water_id`);

CREATE VIEW `用户缴费情况` AS SELECT DISTINCT
    rent.id, 
    rent.create_time, 
    users.user_username, 
    water.water_count, 
    water.water_time, 
    water.water_status, 
    water.water_beforecount, 
    price.waterprice, 
    price.powerprice, 
    power.power_count, 
    power.power_time, 
    power.power_status, 
    power.power_beforecount
FROM
    rent,
    users,
    water,
    price,
    power
WHERE
    rent.user_id = users.user_id AND
    rent.price_id = price.price_id;
———————————————————————————————————————————

好像外键设反了，作如下修改

ALTER TABLE `test`.`power` DROP FOREIGN KEY `fk_power_rent_1`;
ALTER TABLE `test`.`water` DROP FOREIGN KEY `fk_water_rent_1`;
ALTER TABLE `test`.`admin` DROP FOREIGN KEY `fk_admin_rent_1`;
ALTER TABLE `test`.`price` DROP FOREIGN KEY `fk_price_rent_1`;
ALTER TABLE `test`.`users` DROP FOREIGN KEY `fk_users_rent_1`;

ALTER TABLE `test`.`rent` 
ADD CONSTRAINT `fk_power_rent_1` FOREIGN KEY (`power_id`) REFERENCES `test`.`power` (`power_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT `fk_water_rent_1` FOREIGN KEY (`water_id`) REFERENCES `test`.`water` (`water_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT `fk_admin_rent_1` FOREIGN KEY (`admin_id`) REFERENCES `test`.`admin` (`admin_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT `fk_price_rent_1` FOREIGN KEY (`price_id`) REFERENCES `test`.`price` (`price_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT `fk_users_rent_1` FOREIGN KEY (`user_id`) REFERENCES `test`.`users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;