（转载）数据库表设计-水电费缴费系统（oracle）

1 --建表
2 --管理人员表 admin
3 create table admin(
4 admin_id varchar2(3) not null,
5 admin_loginname varchar2(8) not null,
6 admin_password varchar2(6) not null,
7 admin_username varchar2(4) not null,
8 constraint pk_admin primary key(admin_id)
9 );
10 comment on column admin.admin_id is '管理员编号';
11 comment on column admin.admin_loginname is '管理员登录用户名';
12 comment on column admin.admin_password is '管理员登录密码';
13 comment on column admin.admin_username is '管理员姓名';
14 --用户基本信息表 user
15 create table users(
16 user_id varchar2(10) not null,
17 user_loginname varchar2(8) not null,
18 user_password varchar2(6) not null,
19 user_username varchar2(4) not null,
20 user_address varchar2(20) not null,
21 user_phone number(11) not null,
22 constraint pk_users primary key(user_id)
23 );
24 comment on column users.user_id is '用户编号';
25 comment on column users.user_loginname is '用户登录名';
26 comment on column users.user_password is '用户登录密码';
27 comment on column users.user_username is '用户姓名';
28 comment on column users.user_address is '用户地址';
29 comment on column users.user_phone is '用户电话';
30 --水表 water
31 create table water(
32 water_id varchar2(10) not null,
33 userid varchar2(10) not null,
34 water_count number(3,2) not null,
35 water_time date not null,
36 water_status number(1) not null,
37 water_priceid varchar2(10) not null,
38 water_beforecount number(3,2) not null,
39 constraint pk_water primary key(water_id)
40 );
41 comment on column water.water_id is '水表编号';
42 comment on column water.userid is '用户编号';
43 comment on column water.water_count is '水表跑数';
44 comment on column water.water_time is '水表时间';
45 comment on column water.water_status is '水费缴费状态';
46 comment on column water.water_priceid is '水费价格编号';
47 comment on column water.water_beforecount is '上月水表跑数';
48 --水表 power
49 create table power(
50 power_id varchar2(10) not null,
51 userid varchar2(10) not null,
52 power_count number(3,2) not null,
53 power_time date not null,
54 power_status number(1) not null,
55 power_priceid varchar2(10) not null,
56 power_beforecount number(3,2) not null,
57 constraint pk_power primary key(power_id)
58 );
59 comment on column power.power_id is '电表编号';
60 comment on column water.userid is '用户编号';
61 comment on column power.power_count is '电表跑数';
62 comment on column power.power_time is '电表时间';
63 comment on column power.power_status is '电费缴费状态';
64 comment on column power.power_priceid is '电费价格编号';
65 comment on column power.power_beforecount is '上月电表跑数';
66 --价格表 money
67 create table price(
68 price_id varchar2(10) not null,
69 price_time date not null,
70 isactive number(1) not null,
71 waterprice number(2,2) not null,
72 powerprice number(2,2) not null,
73 constraint pk_price primary key(price_id)
74 );
75 comment on column price.price_id is '价格编号';
76 comment on column price.price_time is '价格日期';
77 comment on column price.isactive is '价格状态';
78 comment on column price.waterprice is '水费价格';
79 comment on column price.powerprice is '电费价格';