Java开发物业管理系统：数据库表单设计

按照功能分为：

• 用户表
• 费用表
• 投诉建议表

第一步创建数据库：

CREATE DATABASE yellowstar;

用户表

创建用户表

表名：yw_users

CREATE TABLE IF NOT EXISTS yw_users (
    uid INT UNSIGNED NOT NULL AUTO_INCREMENT KEY COMMENT 'UID',
    username VARCHAR(20) NOT NULL UNIQUE comment '用户名',
    password CHAR(32) NOT NULL COMMENT '密码：md5加密',
    user_type TINYINT UNSIGNED NOT NULL COMMENT '用户类型',
    create_time DATE NOT NULL COMMENT '创建时间'
);

type字段说明：

• 1:超级管理员
• 2:物业
• 3:业主

插入测试数据

INSERT INTO yw_users
(username,password,user_type,create_time)
VALUES
('user1',123456,1,20181101),
('user2',123456,2,20181201),
('user3',123456,3,20181230);

业主房产信息表

房产信息表

表名：yw_houses

CREATE TABLE IF NOT EXISTS yw_houses (
    hid INT UNSIGNED NOT NULL AUTO_INCREMENT KEY COMMENT '房产号',
    username VARCHAR(20) NOT NULL comment '房产持有人',
    house_type VARCHAR(20) NOT NULL comment '房产类型',
    area DOUBLE UNSIGNED NOT NULL COMMENT '住房面积',
    buy_date DATE NOT NULL COMMENT '购房时间',
    building_no VARCHAR(20) NOT NULL comment '楼号',
    unit VARCHAR(10) NOT NULL comment '小区单元',
    card_id INT UNSIGNED NOT NULL COMMENT '门牌号',
    phone VARCHAR(11) NOT NULL comment '手机号码',
);

插入测试数据

INSERT INTO yw_houses
(username,house_type,area,buy_date,building_no,unit,card_id,phone)
VALUES
('张三','三室一厅',100,'2019-01-01','1号楼','一单元',902,'158****9177'),
('李四','三室二厅二卫',130,'2017-01-01','8号楼','二单元',102,'152****9127'),
('冠希','一室一厅',50,'2018-01-01','9号楼','一单元',401,'151****9147');

费用表

费用单价表

表名：yw_perprice

CREATE TABLE IF NOT EXISTS yw_perprice (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY COMMENT '单价编号',
    p_type INT UNSIGNED NOT NULL comment '单价类型',
    price DECIMAL(18,2) UNSIGNED NOT NULL comment '单价',
    p_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '定价时间'
);

p_type说明:

• 1:水费
• 2:电费
• 3:燃气费
• 4:停车费
• 5:物业费

插入测试数据

INSERT INTO yw_perprice
(p_type,price)
VALUES
(1,3.45),
(2,0.5),
(3,1.64),
(4,5),
(5,50);

水费、电费、燃气费、物业费表

表名：yw_totalprice

CREATE TABLE IF NOT EXISTS yw_totalprice (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY COMMENT '编号',
    hid INT UNSIGNED NOT NULL  COMMENT '外键房产id',
    p_type  INT UNSIGNED NOT NULL comment '单价类型',
    price DECIMAL(18,2) UNSIGNED NOT NULL comment '单价',
    amount DECIMAL(18,2) UNSIGNED NOT NULL comment '用量',
    money DECIMAL(18,2) UNSIGNED NOT NULL comment '总额',
    buy_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '消费时间',
    FOREIGN KEY(hid) REFERENCES yw_houses(hid)
);

注意：这里使用了外键

插入测试数据

INSERT INTO yw_totalprice
(hid,p_type,amount,price,money)
VALUES
(1,1,30,(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1),30*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(1,1,21,(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1),21*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(1,2,40,(SELECT price FROM yw_perprice WHERE p_type=2 LIMIT 1),40*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(1,2,34,(SELECT price FROM yw_perprice WHERE p_type=2 LIMIT 1),34*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(1,3,23,(SELECT price FROM yw_perprice WHERE p_type=3 LIMIT 1),23*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(1,4,22,(SELECT price FROM yw_perprice WHERE p_type=4 LIMIT 1),22*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(1,4,12,(SELECT price FROM yw_perprice WHERE p_type=4 LIMIT 1),12*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(1,5,12,(SELECT price FROM yw_perprice WHERE p_type=5 LIMIT 1),12*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(1,5,12,(SELECT price FROM yw_perprice WHERE p_type=5 LIMIT 1),12*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(2,1,23,(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1),23*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(2,1,56,(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1),56*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(2,2,16,(SELECT price FROM yw_perprice WHERE p_type=2 LIMIT 1),16*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(2,3,15,(SELECT price FROM yw_perprice WHERE p_type=3 LIMIT 1),15*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(2,3,90,(SELECT price FROM yw_perprice WHERE p_type=3 LIMIT 1),90*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(2,4,44,(SELECT price FROM yw_perprice WHERE p_type=4 LIMIT 1),44*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)),
(2,4,46,(SELECT price FROM yw_perprice WHERE p_type=4 LIMIT 1),46*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1))
;

注意：这里插入数据使用了子查询

查询讲解：
查询所有业主电费：

SELECT hid as '业主物业ID',price as '单价',amount as '用量',money as '金额',buy_date as '时间'
FROM yw_totalprice
WHERE p_type = 1;

查询所有业主电费(增加显示业主姓名)：

SELECT username as '业主物业姓名',price as '单价',amount as '用量',money as '金额',a.buy_date as '时间'
FROM yw_totalprice AS A JOIN yw_houses AS b
ON a.hid = b.hid 
WHERE p_type = 1;

知识点：这里使用了表连接

查询某业主的电费：
在where里增加一个限定条件就可以了

SELECT username as '业主物业姓名',price as '单价',amount as '用量',money as '金额',a.buy_date as '时间'
FROM yw_totalprice AS A JOIN yw_houses AS b
ON a.hid = b.hid 
WHERE p_type = 1 AND a.hid = 1;

其他费用类推，基本就是改改p_type类型，需要什么数据传什么值就可以了。

投诉建议表（待添加）