Java development of property management system: Design of database forms

According to the function, it is divided into:

  • User table
  • Fee schedule
  • Complaint suggestion form

The first step is to create a database:

CREATE DATABASE yellowstar;

User table

Create user table

Table name: YW Ou 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 'User name',
    password CHAR(32) NOT NULL COMMENT 'Password: md5 encryption',
    user_type TINYINT UNSIGNED NOT NULL COMMENT 'customer type',
    create_time DATE NOT NULL COMMENT 'Creation time'
);

type field description:

  • 1: Super Administrator
  • 2: property
  • 3: owners

Insert test data

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

Owner's property information sheet

Property information sheet

Table name: YW Hou SES

CREATE TABLE IF NOT EXISTS yw_houses (
    hid INT UNSIGNED NOT NULL AUTO_INCREMENT KEY COMMENT 'Real estate number',
    username VARCHAR(20) NOT NULL comment 'Property owner',
    house_type VARCHAR(20) NOT NULL comment 'Property type',
    area DOUBLE UNSIGNED NOT NULL COMMENT 'Housing area',
    buy_date DATE NOT NULL COMMENT 'Buying time',
    building_no VARCHAR(20) NOT NULL comment 'Building number',
    unit VARCHAR(10) NOT NULL comment 'Cell unit',
    card_id INT UNSIGNED NOT NULL COMMENT 'House number',
    phone VARCHAR(11) NOT NULL comment 'Phone number',
);

Insert test data

INSERT INTO yw_houses
(username,house_type,area,buy_date,building_no,unit,card_id,phone)
VALUES
('Zhang San','Three rooms and one hall',100,'2019-01-01','1 Building No.','Unit',902,'158****9177'),
('Li Si','Three rooms, two halls and two bathrooms',130,'2017-01-01','8 Building No.','Unit two',102,'152****9127'),
('Guanxi','One room and one hall',50,'2018-01-01','9 Building No.','Unit',401,'151****9147');

Fee schedule

Cost unit price table

Table name: YW Ou price

CREATE TABLE IF NOT EXISTS yw_perprice (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY COMMENT 'Unit price number',
    p_type INT UNSIGNED NOT NULL comment 'Unit price type',
    price DECIMAL(18,2) UNSIGNED NOT NULL comment 'Unit Price',
    p_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT 'Pricing Time'
);

P? Type Description:

  • 1: water charges
  • 2: electricity charges
  • 3: gas bill
  • 4: parking fee
  • 5: property fee

Insert test data

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

Water, electricity, gas and property fees

Table name: yw'totalprice

CREATE TABLE IF NOT EXISTS yw_totalprice (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY COMMENT 'number',
    hid INT UNSIGNED NOT NULL  COMMENT 'Foreign key real estate id',
    p_type  INT UNSIGNED NOT NULL comment 'Unit price type',
    price DECIMAL(18,2) UNSIGNED NOT NULL comment 'Unit Price',
    amount DECIMAL(18,2) UNSIGNED NOT NULL comment 'Consumption',
    money DECIMAL(18,2) UNSIGNED NOT NULL comment 'Total',
    buy_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT 'dissipate',
    FOREIGN KEY(hid) REFERENCES yw_houses(hid)
);

Note: foreign keys are used here

Insert test data

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

Note: subqueries are used to insert data here

Inquiry and explanation:
Query all electricity charges of the owner:

SELECT hid as 'Owner property ID',price as 'Unit Price',amount as 'Consumption',money as 'Amount of money',buy_date as 'time'
FROM yw_totalprice
WHERE p_type = 1;

Query all electricity charges of the owner (add the name of the owner):

SELECT username as 'Name of property owner',price as 'Unit Price',amount as 'Consumption',money as 'Amount of money',a.buy_date as 'time'
FROM yw_totalprice AS A JOIN yw_houses AS b
ON a.hid = b.hid 
WHERE p_type = 1;


Knowledge point: table join is used here

Query the electricity charge of an owner:
Just add a qualification to where

SELECT username as 'Name of property owner',price as 'Unit Price',amount as 'Consumption',money as 'Amount of money',a.buy_date as 'time'
FROM yw_totalprice AS A JOIN yw_houses AS b
ON a.hid = b.hid 
WHERE p_type = 1 AND a.hid = 1;

Other expenses and so on are basically to change the P ﹐ type type and transfer whatever data is needed.

Complaint suggestion form (to be added)

Tags: Java Database

Posted on Sat, 30 Nov 2019 06:15:57 -0800 by pirolilla