You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 
 
gzf/doc/script.sql

436 lines
12 KiB

create table RefuseApply
(
id int identity
primary key,
apply_id int,
content varchar(255)
)
go
create table ads
(
id int identity
constraint PK__ads__3213E83FC6ED47E1
primary key,
title nvarchar(255),
url nvarchar(255),
thumb nvarchar(255),
created nvarchar(255),
updated int,
status tinyint,
intro nvarchar(255)
)
go
exec sp_addextendedproperty 'MS_Description', '主键编号', 'SCHEMA', 'dbo', 'TABLE', 'ads', 'COLUMN', 'id'
go
exec sp_addextendedproperty 'MS_Description', '列名', 'SCHEMA', 'dbo', 'TABLE', 'ads', 'COLUMN', 'title'
go
exec sp_addextendedproperty 'MS_Description', '列值', 'SCHEMA', 'dbo', 'TABLE', 'ads', 'COLUMN', 'url'
go
exec sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', 'dbo', 'TABLE', 'ads', 'COLUMN', 'created'
go
exec sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', 'dbo', 'TABLE', 'ads', 'COLUMN', 'updated'
go
exec sp_addextendedproperty 'MS_Description', '状态', 'SCHEMA', 'dbo', 'TABLE', 'ads', 'COLUMN', 'status'
go
create table application_material
(
id int identity
constraint application_material_pk
primary key nonclustered,
form_name nvarchar(10) not null,
form_type nvarchar(10) not null,
form_order int,
material_name nvarchar(20) not null
)
go
exec sp_addextendedproperty 'MS_Description', '申请资料表', 'SCHEMA', 'dbo', 'TABLE', 'application_material'
go
exec sp_addextendedproperty 'MS_Description', '资料id', 'SCHEMA', 'dbo', 'TABLE', 'application_material', 'COLUMN', 'id'
go
exec sp_addextendedproperty 'MS_Description', '表单name', 'SCHEMA', 'dbo', 'TABLE', 'application_material', 'COLUMN',
'form_name'
go
exec sp_addextendedproperty 'MS_Description', '表单类型', 'SCHEMA', 'dbo', 'TABLE', 'application_material', 'COLUMN',
'form_type'
go
exec sp_addextendedproperty 'MS_Description', '表单顺序', 'SCHEMA', 'dbo', 'TABLE', 'application_material', 'COLUMN',
'form_order'
go
exec sp_addextendedproperty 'MS_Description', '资料名', 'SCHEMA', 'dbo', 'TABLE', 'application_material', 'COLUMN',
'material_name'
go
create unique index application_material_form_name_uindex
on application_material (form_name)
go
create table apply
(
id int identity
constraint PK__apply__3213E83FEA5D6CEB
primary key,
hid int,
userid int,
state int,
type int,
created nvarchar(255),
attachfile nvarchar(255),
stateid int,
pid int,
cid int,
aid int,
message text
)
go
create table cates
(
id int identity
constraint PK__cates__3213E83F0A082218
primary key,
title nvarchar(255),
thumb nvarchar(255),
description nvarchar(max),
created nvarchar(255),
updated int,
type int,
status tinyint,
pid int
)
go
exec sp_addextendedproperty 'MS_Description', '主键编号', 'SCHEMA', 'dbo', 'TABLE', 'cates', 'COLUMN', 'id'
go
exec sp_addextendedproperty 'MS_Description', '类别名称', 'SCHEMA', 'dbo', 'TABLE', 'cates', 'COLUMN', 'title'
go
exec sp_addextendedproperty 'MS_Description', '封面', 'SCHEMA', 'dbo', 'TABLE', 'cates', 'COLUMN', 'thumb'
go
exec sp_addextendedproperty 'MS_Description', '描述', 'SCHEMA', 'dbo', 'TABLE', 'cates', 'COLUMN', 'description'
go
exec sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', 'dbo', 'TABLE', 'cates', 'COLUMN', 'created'
go
exec sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', 'dbo', 'TABLE', 'cates', 'COLUMN', 'updated'
go
exec sp_addextendedproperty 'MS_Description', '状态', 'SCHEMA', 'dbo', 'TABLE', 'cates', 'COLUMN', 'status'
go
create table community
(
id int identity
constraint community_pk
primary key nonclustered,
name nvarchar(30) not null,
image nvarchar(255) not null,
pid int not null,
cid int not null,
aid int not null,
address nvarchar(100) not null,
longitude nvarchar(20) not null,
latitude nvarchar(20) not null,
house_type nvarchar(20) not null,
description nvarchar(512) not null,
type int not null,
user_id int not null
)
go
exec sp_addextendedproperty 'MS_Description', '社区表', 'SCHEMA', 'dbo', 'TABLE', 'community'
go
exec sp_addextendedproperty 'MS_Description', '社区id', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'id'
go
exec sp_addextendedproperty 'MS_Description', '社区名称', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'name'
go
exec sp_addextendedproperty 'MS_Description', '社区图片', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'image'
go
exec sp_addextendedproperty 'MS_Description', '', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'pid'
go
exec sp_addextendedproperty 'MS_Description', '', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'cid'
go
exec sp_addextendedproperty 'MS_Description', '', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'aid'
go
exec sp_addextendedproperty 'MS_Description', '详细地址', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'address'
go
exec sp_addextendedproperty 'MS_Description', '经度', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'longitude'
go
exec sp_addextendedproperty 'MS_Description', '纬度', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'latitude'
go
exec sp_addextendedproperty 'MS_Description', '主要户型', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'house_type'
go
exec sp_addextendedproperty 'MS_Description', '详细描述', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'description'
go
exec sp_addextendedproperty 'MS_Description', '社区类型', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'type'
go
exec sp_addextendedproperty 'MS_Description', '发布人', 'SCHEMA', 'dbo', 'TABLE', 'community', 'COLUMN', 'user_id'
go
create table house
(
id int identity
constraint PK__house__3213E83F40F3B488
primary key,
rooname nvarchar(255),
size nvarchar(11),
thunb nvarchar(255),
price int,
description nvarchar(max),
created nvarchar(255),
content nvarchar(max),
lat nvarchar(255),
lng nvarchar(255),
userid int,
username nvarchar(255),
pid int,
cid int,
aid int,
state int,
address nvarchar(255),
cate nvarchar(255),
community_id int not null
constraint FK42AD700C0E01536
references community,
total int not null,
remain int not null
)
go
exec sp_addextendedproperty 'MS_Description', '社区id', 'SCHEMA', 'dbo', 'TABLE', 'house', 'COLUMN', 'community_id'
go
exec sp_addextendedproperty 'MS_Description', '总量', 'SCHEMA', 'dbo', 'TABLE', 'house', 'COLUMN', 'total'
go
exec sp_addextendedproperty 'MS_Description', '余量', 'SCHEMA', 'dbo', 'TABLE', 'house', 'COLUMN', 'remain'
go
create table news
(
id int identity
constraint PK__news__3214EC2780F5B1B1
primary key,
title nvarchar(255) not null,
description nvarchar(max) not null,
contents nvarchar(max) not null,
thumb nvarchar(255) not null,
cateid int,
created nvarchar(255),
click int,
province int,
city int,
area int,
userid int,
address nvarchar(255),
zan int,
fav int,
attachfile nvarchar(255)
)
go
create table refuse_apply
(
id int identity
constraint refuse_apply_pk
primary key nonclustered,
apply_id int not null,
content nvarchar(50) not null
)
go
exec sp_addextendedproperty 'MS_Description', '拒绝理由表', 'SCHEMA', 'dbo', 'TABLE', 'refuse_apply'
go
exec sp_addextendedproperty 'MS_Description', '理由id', 'SCHEMA', 'dbo', 'TABLE', 'refuse_apply', 'COLUMN', 'id'
go
exec sp_addextendedproperty 'MS_Description', '申请id', 'SCHEMA', 'dbo', 'TABLE', 'refuse_apply', 'COLUMN', 'apply_id'
go
exec sp_addextendedproperty 'MS_Description', '理由', 'SCHEMA', 'dbo', 'TABLE', 'refuse_apply', 'COLUMN', 'content'
go
create table region
(
id smallint not null
primary key,
parent_id smallint not null,
region_name nvarchar(120) not null,
region_type tinyint not null,
agency_id smallint not null
)
go
create index agency_id
on region (agency_id)
go
create index parent_id
on region (parent_id)
go
create index region_type
on region (region_type)
go
create table report
(
id int identity
constraint PK__report__3213E83FDC3E130B
primary key,
content nvarchar(255),
userid int,
mid int,
created nvarchar(255),
state int
)
go
create table user_material
(
id int identity
constraint user_material_pk
primary key nonclustered,
user_id int not null,
form_id int not null,
form_value nvarchar(100) not null,
apply_id int not null
)
go
exec sp_addextendedproperty 'MS_Description', '用户填写申请资料表', 'SCHEMA', 'dbo', 'TABLE', 'user_material'
go
exec sp_addextendedproperty 'MS_Description', '填写id', 'SCHEMA', 'dbo', 'TABLE', 'user_material', 'COLUMN', 'id'
go
exec sp_addextendedproperty 'MS_Description', '填写用户', 'SCHEMA', 'dbo', 'TABLE', 'user_material', 'COLUMN', 'user_id'
go
exec sp_addextendedproperty 'MS_Description', '填写资料id', 'SCHEMA', 'dbo', 'TABLE', 'user_material', 'COLUMN', 'form_id'
go
exec sp_addextendedproperty 'MS_Description', '填写表单值', 'SCHEMA', 'dbo', 'TABLE', 'user_material', 'COLUMN', 'form_value'
go
exec sp_addextendedproperty 'MS_Description', '申请id', 'SCHEMA', 'dbo', 'TABLE', 'user_material', 'COLUMN', 'apply_id'
go
create table users
(
id int identity
constraint PK__users__3213E83F71AD4D69
primary key,
username nvarchar(60),
password nvarchar(32),
sex tinyint,
birthday date,
address nvarchar(128),
email nvarchar(60),
qq nvarchar(20),
tel nvarchar(20),
money decimal(10, 2),
thumb nvarchar(255),
grade int,
type int,
created nvarchar(255),
updated nvarchar(32),
status tinyint,
age int,
ecard nvarchar(255),
fromid int,
fromname nvarchar(255),
company nvarchar(255),
realname nvarchar(255),
position nvarchar(255),
signature nvarchar(255),
pid int,
cid int,
aid int
)
go
exec sp_addextendedproperty 'MS_Description', '主键ID', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'id'
go
exec sp_addextendedproperty 'MS_Description', '用户名', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'username'
go
exec sp_addextendedproperty 'MS_Description', '密码', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'password'
go
exec sp_addextendedproperty 'MS_Description', '性别', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'sex'
go
exec sp_addextendedproperty 'MS_Description', '生日', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'birthday'
go
exec sp_addextendedproperty 'MS_Description', '地址', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'address'
go
exec sp_addextendedproperty 'MS_Description', '邮箱', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'email'
go
exec sp_addextendedproperty 'MS_Description', 'qq', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'qq'
go
exec sp_addextendedproperty 'MS_Description', '电话', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'tel'
go
exec sp_addextendedproperty 'MS_Description', '账户余额', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'money'
go
exec sp_addextendedproperty 'MS_Description', '头像', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'thumb'
go
exec sp_addextendedproperty 'MS_Description', '类型', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'type'
go
exec sp_addextendedproperty 'MS_Description', '注册时间', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'created'
go
exec sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'updated'
go
exec sp_addextendedproperty 'MS_Description', '状态', 'SCHEMA', 'dbo', 'TABLE', 'users', 'COLUMN', 'status'
go
create index email
on users (email)
go
create unique index user_name
on users (username)
go