《社区服务数据库设计.doc》由会员分享，可在线阅读，更多相关《社区服务数据库设计.doc（21页珍藏版）》请在三一文库上搜索。
1、数据列表用户表sq_user列名字段名称数据类型（精度范围）空 /非空约束条件备注用户 ID手机号密码权限所属社区姓名住址文化程度Apk 版本号性别年龄最近访问时间USERIDPHONENUMBERPASSWORDAUTHORITYCOMMUNITYNAMEADDRESSCULTURAL_DEGREEAPK_VERSIONSEXAGEVISIT_TIMEVARCHAR2(32)VARCHAR2(32)VARCHAR2(60)VARCHAR2(10)VARCHAR2(32)VARCHAR2(20)VARCHAR2(50)VARCHAR2(32)VARCHAR2(10)VARCHAR2(4)NUM
2、BERDATE非空非空非空PK两种服务器- Create tablecreatetable(SQ_USERuseridVARCHAR2( 32 )notnull,phonenumberVARCHAR2( 32 )notnull,passwordVARCHAR2( 60 )notnull,authorityVARCHAR2( 10 ),communityVARCHAR2( 32 ),nameVARCHAR2( 20 ),addressVARCHAR2( 50 ),cultural_degreeVARCHAR2( 32 ),apk_versionVARCHAR2( 10 ),visit_timeD
3、ATE,sexVARCHAR2( 4 ),ageNUMBER)tablespacepctfreeinitransmaxtransstorageUSERS101255(initial64 Kminextentsmaxextents1unlimited);- Create/Recreate primary, unique and foreign key constraints altertable SQ_USERaddconstraintSQ_USER_KEYprimarykeyusingindextablespaceUSERSpctfree10initrans2maxtrans255storag
4、e(USERID)(initial64 Kminextentsmaxextents1unlimited);社区信息表sq_community列名字段名称数据类型（精度范围）空 /非空约束条件备注社区IDCOMMUNITYIDVARCHAR2(32)非空PK社区名称COMMUNITYNAMEVARCHAR2(32)非空所属区县AREANAMEVARCHAR2(20)详细地址ADDRESSNAMEVARCHAR2(50)两种社区编号COMMUNITYNUMBERVARCHAR2(32)社区负责人CHIEFNAMEVARCHAR2(20)负责人电话CHIEFTELVARCHAR2(32)- Crea
5、te tablecreatetable(SQ_COMMUNITYcommunityidcommunitnameareanameaddressnamecommunitynumberchiefnamechieftelVARCHAR2( 32 )VARCHAR2( 32 ),VARCHAR2( 20 ),VARCHAR2( 50 ),VARCHAR2( 32 ),VARCHAR2( 20 ),VARCHAR2( 32 )notnull,)tablespaceUSERSpctfree10initrans1maxtrans255storage(initial64 Kminextents1maxexten
6、tsunlimited);- Create/Recreate primary, unique and foreign key constraints altertable SQ_COMMUNITYaddconstraintKEY_COMMUNITYprimaryusingindexkey(COMMUNITYID)tablespaceUSERSpctfree10initrans2maxtrans255storage(initial64 Kminextents1maxextentsunlimited);用户关注表 sq_attention列名字段名称数据类型（精度范围）空 /非空约束条件备注标识
7、IDIDVARCHAR2(32)非空PK用户 idUSER_IDVARCHAR2(32)FK关注 idATTENTION_DIC_IDVARCHAR2(32)FK- Create tablecreatetableSQ_ATTENTION(iduser_idattention_dic_idVARCHAR2( 32 )notVARCHAR2( 32 )notVARCHAR2( 32 )nullnull,)tablespaceUSERSpctfree10initrans1maxtrans255storage(initial64 Kminextents1maxextentsunlimited);- C
8、reate/Recreate primary, unique and foreign key constraints altertable SQ_ATTENTIONaddconstraintSQ_ATTENTION_KEYprimaryusingindextablespaceUSERSpctfree10initrans2key( ID)maxtrans255storage(initial64 Kminextents1maxextentsunlimited);altertableSQ_ATTENTIONaddconstraintSQ_ATTENTION_FKEYforeignkey(ATTENT
9、ION_DIC_ID)referencesSQ_ATTENTION_DIC (ATTENTION_DIC_ID)ondeletecascade;altertableSQ_ATTENTIONaddconstraintSQ_ATTENTION_FKEY2foreignkey(USER_ID)referencesSQ_USER (USERID)ondeletecascade;视频信息表 sq_ videoMessage列名字段名称数据类型（精度范围）视频 idVIDEO_IDVARCHAR2(32)视频名称VIDEO_NAMEVARCHAR2(32)视频大小VIDEO_SIZWVARCHAR2(32
10、)视频时长VIDEO_LENGTHVARCHAR2(20)录入用户UPLOAD_USER_IDVARCHAR2(32)录入时间UPLOAD_TIMEDATE审核标记UPLOAD_AUDITVARCHAE2(4)- Create tablecreatetableSQ_VIDEO(video_idVARCHAR2( 32 )notnull,video_nameVARCHAR2( 32 ),video_sizeVARCHAR2( 32 ),video_lengthVARCHAR2( 20 ),upload_user_idVARCHAR2( 32 ),upload_timeDATE,upload_au
11、ditVARCHAR2( 4)tablespaceUSERSpctfree10initrans1maxtrans255storage(initial64 Kminextents1maxextentsunlimited);- Create/Recreate primary, unique and foreign key constraints空 /非空约束条件备注非空PK非空altertableSQ_VIDEOaddconstraintSQ_VIDEO_KEYprimarykey (VIDEO_ID)usingindextablespaceUSERSpctfree10initrans2maxtr
12、ans255storage(initial64 Kminextents1maxextentsunlimited);图片信息表 sq_ picture列名字段名称数据类型（精度范围）空 /非空约束条件备注图片 idPICTURE_IDVARCHAR2(32)非空PK图片名称PICTURE_NAMEVARCHAR2(32)非空录入用户UPLOAD_USER_IDVARCHAR2(32)审核标记UPLOAD_AUDITVARCHAE2(4)录入时间UPLOAD_TIMEDATE- Create tablecreatetableSQ_PICTURE(picture_idVARCHAR2( 32 )no
13、tnull,picture_nameVARCHAR2( 32 ),upload_user_idVARCHAR2( 32 ),upload_timeDATE,upload_auditVARCHAR2( 4)tablespaceUSERSpctfree10initrans1maxtrans255storage(initial64 Kminextents1maxextentsunlimited);- Create/Recreate primary, unique and foreign key constraints altertable SQ_PICTUREaddconstraintSQ_PICT
14、URE_KEYusingindextablespaceUSERSprimarykey(PICTURE_ID)pctfree10initrans2maxtrans255storage(initial64 Kminextents1maxextentsunlimited);推送信息表 sq_pushmessage列名字段名称数据类型（精度范围）信息 idMESSAGE_IDVARCHAR2(32)信息标题MESSAGE_TITLEVARCHAR2(70)信息正文MESSAGE_BODYVARCHAR2(500)信息图片 IDPICTURE_IDVARCHAR2(32)推送级别PUSH_LEVELVA
15、RCHAR2(10)信息视频 IDVIDEO_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 tablecreatetableSQ_PUSHMESSAGE(message_idVARCHAR2( 32 )notnul
16、l,message_titleVARCHAR2( 70 ),message_bodyVARCHAR2( 500 ),picture_idVARCHAR2( 32 ),push_levelVARCHAR2( 10 ),video_idVARCHAR2( 32 ),classificationVARCHAR2( 20 ),push_timeDATE,message_urlVARCHAR2( 50 ),upload_user_idVARCHAR2( 32 ),upload_timeDATE,suit_sexVARCHAR2( 4 ),suit_age_upNUMBER,suit_age_downNU
17、MBER空 /非空约束条件备注非空PK全推)tablespaceUSERSpctfree10initrans1maxtrans255storage(initial64 Kminextents1maxextentsunlimited);- Create/Recreate primary, unique and foreign key constraints altertable SQ_PUSHMESSAGEaddconstraintSQ_PUSHMESSAGEprimaryusingindexkey(MESSAGE_ID)tablespaceUSERSpctfree10initrans2maxt
18、rans255storage(initial64 Kminextents1maxextentsunlimited);公告信息表 sq_announcement列名字段名称数据类型（精度范围）空 /非空约束条件备注公告 idANNOUNCEMENT_IDVARCHAR2(32)非空PK公告标题ANNOUNCEMENTVARCHAR2(70)_TITLE公告正文ANNOUNCEMENTVARCHAR2(700)_BODY公告图片 IDANNOUNCEMENTVARCHAR2(32)_PICTURE_ID公告视频 IDANNOUNCEMENTVARCHAR2(32)_VIDEO_ID公告时间ANNO
19、UNCEMENT_TIMDATEE公告人ANNOUNCEMENT_USEVARCHAR2(32)R_ID录入用户UPLOAD_USERVARCHAR2(32)录入时间UPLOAD_TIMEDATE- Create tablecreatetableSQ_ANNOUNCEMENT(announcement_idVARCHAR2( 32 )notnull,announcement_titleVARCHAR2( 70 ),announcement_bodyVARCHAR2( 700 ),announcement_picture_idVARCHAR2( 32 ),announcement_video_i
20、dVARCHAR2( 32 ),announcement_timeDATE,announcement_user_idVARCHAR2( 32 ),upload_user_idVARCHAR2( 32 ),upload_timeDATE)tablespaceUSERSpctfree10initrans1maxtrans255storage(initial64 Kminextents1maxextentsunlimited);- Create/Recreate primary, unique and foreign key constraints altertable SQ_ANNOUNCEMEN
21、TaddconstraintSQ_ANNOUNCEMENT_KEYprimarykey(ANNOUNCEMENT_ID)usingindextablespaceUSERSpctfree10initrans2maxtrans255storage(initial64 Kminextents1maxextentsunlimited);APK 信息表 sq_apk列名APKIDAPK 版本号APK 录入人APK 录入时间字段名称APK_IDAPK_VERSIONAPK_USER_IDAPK_TIME数据类型（精度范围）空 /非空约束条件备注VARCHAR2(32)非空PKVARCHAR(10)非空VA
22、RCHAR(60)DA TE更新信息CHANGE_TEXTVARCHAR(60)APKAPKBLOB- Create tablecreatetable(SQ_APKapk_idVARCHAR2( 32 ) notnull,apk_versionVARCHAR2( 10 ),apk_user_idVARCHAR2( 32 ),apk_timeDATE,change_textVARCHAR2( 60 ),apkBLOB)tablespacepctfreeinitransmaxtransstorageUSERS101255(initial64 Kminextentsmaxextents1unlimi
23、ted);- Create/Recreate primary, unique and foreign key constraints altertable SQ_APKaddconstraintSQ_APK_KEYprimarykeyusingindextablespaceUSERSpctfree10initrans2maxtrans255storage(APK_ID)(initial64 Kminextentsmaxextents1unlimited);列名关注字典表sq_attention_dic字段名称数据类型（精度范围）空 /非空约束条件备注关注idATTENTION_DIC_IDVA
24、RCHAR2(32)非空PK用户关注USER_ATTENTIONVARCHAR(20)非空- Create tablecreatetableSQ_ATTENTION_DIC(attention_dic_idVARCHAR2( 32 ) notnull,user_attentionVARCHAR2( 20 )tablespaceUSERSpctfree10initrans1maxtrans255storage(initial64 Kminextents1maxextentsunlimited);- Create/Recreate primary, unique and foreign key c
25、onstraintsaltertableSQ_ATTENTION_DICaddconstraintSQ_ATTENTION_DIC_KEYprimarykey(ATTENTION_DIC_ID)usingindextablespaceUSERSpctfree10initrans2maxtrans255storage(initial64 Kminextentsmaxextents1unlimited);推送用户接收关联表（记录推送信息推送到哪些用户）sq_ push_receive列名字段名称数据类型（精度范围）空 /非空约束条件备注标记idPUSH_RECEIVE_IDVARCHAR2(32)
26、非空PK用户idUSER_IDVARCHAR(32)非空信息idMESSAGE_IDVARCHAR(32)非空接收时间RECEIVE_TIMEDA TE非空- Create tablecreatetableSQ_PUSH_RECEIVE(push_receive_idVARCHAR2( 32 ) not null ,user_idVARCHAR2( 32 ),message_idVARCHAR2( 32 )tablespaceUSERSpctfree10initrans1maxtrans255storage(initial64 Kminextents1maxextentsunlimited);
27、- Create/Recreate primary, unique and foreign key constraintsaltertableSQ_PUSH_RECEIVEaddconstraintSQ_PUSH_RECEIVE_KEYprimarykey(PUSH_RECEIVE_ID)usingindextablespaceUSERSpctfree10initrans2maxtrans255storage(initial64 Kminextents1maxextentsunlimited);altertableSQ_PUSH_RECEIVEaddconstraintSQ_PUSH_RECEIVE_FKEYreferencesSQ_PUSHMESSAGE (MESSAGE_ID)foreignonkey delete(MESSAGE_ID)cascade;