社区服务数据库设计

1、1.1.1. 数据列表l 用户表sq_user列名字段名称数据类型（精度范围）空/非空约束条件备注用户IDUSERID VARCHAR2(32)非空PK手机号PHONENUMBERVARCHAR2(32)非空密码PASSWORDVARCHAR2(60)非空权限AUTHORITYVARCHAR2(10)两种所属社区COMMUNITYVARCHAR2(32)姓名NAMEVARCHAR2(20)住址ADDRESSVARCHAR2(50)文化程度CULTURAL_DEGREEVARCHAR2(32)Apk版本号APK_VERSIONVARCHAR2(10)性别SEXVARCHAR2(4)年龄AGENU

2、MBER最近访问时间VISIT_TIMEDATE服务器- Create tablecreate table SQ_USER( userid VARCHAR2(32) not null, phonenumber VARCHAR2(32) not null, password VARCHAR2(60) not null, authority VARCHAR2(10), community VARCHAR2(32), name VARCHAR2(20), address VARCHAR2(50), cultural_degree VARCHAR2(32), apk_version VARCHAR2(

3、10), visit_time DATE, sex VARCHAR2(4), age NUMBER)tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table SQ_USER add constraint SQ_USER_KEY primary key (USERID) using inde

4、x tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );l 社区信息表sq_community列名字段名称数据类型（精度范围）空/非空约束条件备注社区IDCOMMUNITYID VARCHAR2(32)非空PK社区名称COMMUNITYNAMEVARCHAR2(32)非空所属区县AREANAMEVARCHAR2(20)详细地址ADDRESSNAMEVARCHAR2(50)两种社区编号COMMUNITYNUMBERVARCHAR2

5、(32)社区负责人CHIEFNAMEVARCHAR2(20)负责人电话CHIEFTELVARCHAR2(32)- Create tablecreate table SQ_COMMUNITY( communityid VARCHAR2(32) not null, communitname VARCHAR2(32), areaname VARCHAR2(20), addressname VARCHAR2(50), communitynumber VARCHAR2(32), chiefname VARCHAR2(20), chieftel VARCHAR2(32)tablespace USERS p

6、ctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table SQ_COMMUNITY add constraint KEY_COMMUNITY primary key (COMMUNITYID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 st

7、orage ( initial 64K minextents 1 maxextents unlimited );l 用户关注表sq_attention列名字段名称数据类型（精度范围）空/非空约束条件备注标识IDID VARCHAR2(32)非空PK用户idUSER_IDVARCHAR2(32)FK关注idATTENTION_DIC_ID VARCHAR2(32)FK- Create tablecreate table SQ_ATTENTION( id VARCHAR2(32) not null, user_id VARCHAR2(32) not null, attention_dic_id V

8、ARCHAR2(32)tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table SQ_ATTENTION add constraint SQ_ATTENTION_KEY primary key (ID) using index tablespace USERS pctfree 10 ini

9、trans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );alter table SQ_ATTENTION add constraint SQ_ATTENTION_FKEY foreign key (ATTENTION_DIC_ID) references SQ_ATTENTION_DIC (ATTENTION_DIC_ID) on delete cascade;alter table SQ_ATTENTION add constraint SQ_ATTENTION_FKEY2 foreign

10、key (USER_ID) references SQ_USER (USERID) on delete cascade;l 视频信息表sq_ videoMessage列名字段名称数据类型（精度范围）空/非空约束条件备注视频idVIDEO_IDVARCHAR2(32)非空PK视频名称VIDEO_NAMEVARCHAR2(32)非空视频大小VIDEO_SIZWVARCHAR2(32)视频时长VIDEO_LENGTHVARCHAR2(20)录入用户UPLOAD_USER_IDVARCHAR2(32)录入时间UPLOAD_TIMEDATE审核标记UPLOAD_AUDITVARCHAE2(4)- Cre

11、ate tablecreate table SQ_VIDEO( video_id VARCHAR2(32) not null, video_name VARCHAR2(32), video_size VARCHAR2(32), video_length VARCHAR2(20), upload_user_id VARCHAR2(32), upload_time DATE, upload_audit VARCHAR2(4)tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 m

12、axextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table SQ_VIDEO add constraint SQ_VIDEO_KEY primary key (VIDEO_ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );l 图片信息表sq_ picture列名字段

13、名称数据类型（精度范围）空/非空约束条件备注图片idPICTURE_IDVARCHAR2(32)非空PK图片名称PICTURE_NAMEVARCHAR2(32)非空录入用户UPLOAD_USER_IDVARCHAR2(32)审核标记UPLOAD_AUDITVARCHAE2(4)录入时间UPLOAD_TIMEDATE- Create tablecreate table SQ_PICTURE( picture_id VARCHAR2(32) not null, picture_name VARCHAR2(32), upload_user_id VARCHAR2(32), upload_time D

14、ATE, upload_audit VARCHAR2(4)tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table SQ_PICTURE add constraint SQ_PICTURE_KEY primary key (PICTURE_ID) using index tablespac

15、e USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );l 推送信息表sq_pushmessage列名字段名称数据类型（精度范围）空/非空约束条件备注信息idMESSAGE_IDVARCHAR2(32)非空PK信息标题MESSAGE_TITLEVARCHAR2(70)信息正文MESSAGE_BODYVARCHAR2(500)信息图片IDPICTURE_IDVARCHAR2(32)推送级别PUSH_LEVELVARCHAR2(10)全推信息视频IDVI

16、DEO_IDVARCHAR2(32)信息分类CLASSIFICATIONVARCHAR2(20)推送时间PUSH_TIMEDATE信息网址MESSAGE_URLVARCHAR2(50)录入用户UPLOAD_USER_IDVARCHAR2(32)适合性别SUIT_SEXVARCHAR2(4)适合年龄上SUIT_AGE_UPNUMBER适合性别下SUIT_ AGE_DOWNNUMBER录入时间UPLOAD_TIMEDATE- Create tablecreate table SQ_PUSHMESSAGE( message_id VARCHAR2(32) not null, message_titl

17、e VARCHAR2(70), message_body VARCHAR2(500), picture_id VARCHAR2(32), push_level VARCHAR2(10), video_id VARCHAR2(32), classification VARCHAR2(20), push_time DATE, message_url VARCHAR2(50), upload_user_id VARCHAR2(32), upload_time DATE, suit_sex VARCHAR2(4), suit_age_up NUMBER, suit_age_down NUMBER)ta

18、blespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table SQ_PUSHMESSAGE add constraint SQ_PUSHMESSAGE primary key (MESSAGE_ID) using index tablespace USERS pctfree 10 initrans

19、 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );l 公告信息表sq_announcement列名字段名称数据类型（精度范围）空/非空约束条件备注公告idANNOUNCEMENT_IDVARCHAR2(32)非空PK公告标题ANNOUNCEMENT _TITLEVARCHAR2(70)公告正文ANNOUNCEMENT _BODYVARCHAR2(700)公告图片IDANNOUNCEMENT _PICTURE_IDVARCHAR2(32)公告视频IDANNOUNCEMENT _VIDEO_IDVAR

20、CHAR2(32)公告时间ANNOUNCEMENT_TIMEDATE公告人ANNOUNCEMENT_USER_IDVARCHAR2(32)录入用户UPLOAD_USERVARCHAR2(32)录入时间UPLOAD_TIMEDATE- Create tablecreate table SQ_ANNOUNCEMENT( announcement_id VARCHAR2(32) not null, announcement_title VARCHAR2(70), announcement_body VARCHAR2(700), announcement_picture_id VARCHAR2(32)

21、, announcement_video_id VARCHAR2(32), announcement_time DATE, announcement_user_id VARCHAR2(32), upload_user_id VARCHAR2(32), upload_time DATE)tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign k

22、ey constraints alter table SQ_ANNOUNCEMENT add constraint SQ_ANNOUNCEMENT_KEY primary key (ANNOUNCEMENT_ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );l APK信息表sq_apk列名字段名称数据类型（精度范围）空/非空约束条件备注APKIDAPK_ID VARCHAR2(32)非空PKAP

23、K版本号APK_VERSION VARCHAR(10)非空APK录入人APK_USER_IDVARCHAR(60)APK录入时间APK_TIME DATE更新信息CHANGE_TEXT VARCHAR(60)APKAPK BLOB- Create tablecreate table SQ_APK( apk_id VARCHAR2(32) not null, apk_version VARCHAR2(10), apk_user_id VARCHAR2(32), apk_time DATE, change_text VARCHAR2(60), apk BLOB)tablespace USERS p

24、ctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table SQ_APK add constraint SQ_APK_KEY primary key (APK_ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initia

25、l 64K minextents 1 maxextents unlimited );l 关注字典表sq_attention_dic列名字段名称数据类型（精度范围）空/非空约束条件备注关注idATTENTION_DIC_ID VARCHAR2(32)非空PK用户关注USER_ATTENTION VARCHAR(20)非空- Create tablecreate table SQ_ATTENTION_DIC( attention_dic_id VARCHAR2(32) not null, user_attention VARCHAR2(20)tablespace USERS pctfree 10

26、initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );- Create/Recreate primary, unique and foreign key constraints alter table SQ_ATTENTION_DIC add constraint SQ_ATTENTION_DIC_KEY primary key (ATTENTION_DIC_ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minexte