QuickDBD-export.sql

-- Exported from QuickDBD: https://www.quickdatabasediagrams.com/
-- NOTE! If you have used non-SQL datatypes in your design, you will have to change these here.


-- WX User
CREATE TABLE "User" (
    "UserID"  SERIAL  NOT NULL,
    -- User Name
    -- WX 昵称（不是群昵称）
    "Name" string   NOT NULL,
    CONSTRAINT "pk_User" PRIMARY KEY (
        "UserID"
     )
);

-- WX 群
CREATE TABLE "Group" (
    "GroupID"  SERIAL  NOT NULL,
    -- Group Name
    "Name" string   NOT NULL,
    -- Group Comment
    "Comment" string   NOT NULL,
    -- Day range for reporting of being active
    "ActivePeriod" int  DEFAULT 15 NOT NULL,
    -- Day range for reporting of being sleep
    "QuietPeriod" int  DEFAULT 30 NOT NULL,
    -- Number of active users to report
    "ActiveNum" int  DEFAULT 10 NOT NULL,
    -- Number of quiet users to report
    "QuietNum" int  DEFAULT 10 NOT NULL,
    CONSTRAINT "pk_Group" PRIMARY KEY (
        "GroupID"
     )
);

CREATE TABLE "ActivityType" (
    "ActivityTypeID"  SERIAL  NOT NULL,
    "Name" string   NOT NULL,
    -- 贡献	发言总字数
    -- 爱心	发言后又撤回重新修改的总字数
    -- 分享	除纯文字以外，任何其他的分享
    -- 热闹	贴表情包图
    -- .
    -- .
    -- .
    -- .
    -- .
    -- .
    -- .
    "Comment" string   NOT NULL,
    CONSTRAINT "pk_ActivityType" PRIMARY KEY (
        "ActivityTypeID"
     )
);

CREATE TABLE "ActivityLog" (
    "ActivityLogID"  SERIAL  NOT NULL,
    "UserID" int   NOT NULL,
    "GroupID" int   NOT NULL,
    "ActivityTypeID" int   NOT NULL,
    "ActivityDate" dateTime   NOT NULL,
    "Stat" int   NOT NULL,
    CONSTRAINT "pk_ActivityLog" PRIMARY KEY (
        "ActivityLogID"
     )
);

-- User Statistic (View structure)
CREATE TABLE "UserStat" (
    "UserID" int   NOT NULL,
    "GroupID" int   NOT NULL,
    -- 贡献指数	发言总字数累计
    "WordCount" int   NOT NULL,
    -- 爱心指数	发言后又撤回重新修改的总字数累计
    "CorrectionCount" int   NOT NULL,
    -- 分享指数	除纯文字以外，任何其他分享的次数累计
    "ShareCount" int   NOT NULL,
    -- 热闹指数	贴表情包图的次数累计（表情包不统计在分享指数之中）
    "MemeCount" int   NOT NULL
);

ALTER TABLE "ActivityLog" ADD CONSTRAINT "fk_ActivityLog_UserID" FOREIGN KEY("UserID")
REFERENCES "User" ("UserID");

ALTER TABLE "ActivityLog" ADD CONSTRAINT "fk_ActivityLog_GroupID" FOREIGN KEY("GroupID")
REFERENCES "Group" ("GroupID");

ALTER TABLE "ActivityLog" ADD CONSTRAINT "fk_ActivityLog_ActivityTypeID" FOREIGN KEY("ActivityTypeID")
REFERENCES "ActivityType" ("ActivityTypeID");

ALTER TABLE "UserStat" ADD CONSTRAINT "fk_UserStat_UserID" FOREIGN KEY("UserID")
REFERENCES "User" ("UserID");

ALTER TABLE "UserStat" ADD CONSTRAINT "fk_UserStat_GroupID" FOREIGN KEY("GroupID")
REFERENCES "Group" ("GroupID");

CREATE INDEX "idx_User_Name"
ON "User" ("Name");

CREATE INDEX "idx_Group_Name"
ON "Group" ("Name");

QuickDBD-export.svg

<svg class="diagram-root" style="background: #f5f6f6" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink"><style>.diagram-root { font-family: "Helvetica Neue", sans-serif; font-size: 12px; } .t { overflow: visible; } .t-bg { y: 40; fill: #fff;   } .th { fill: #005aa0;   } .tht { fill: #fff; font-size: 14px;  } .tf { fill: #323c46; overflow: visible; } .tf.pk { fill: #005aa0; font-weight: bold; } .tf.fk { font-weight: bold; } .ty { fill: #adb1b5; font-weight: normal; text-anchor: end; } .r { fill: transparent; stroke: #c3c3c3; stroke-width: 1; } .l { stroke: 1; } .i { fill: #c2c7ce; stroke: #c2c7ce; } .i.ix { fill: #afb3b6; stroke: #000; } .fa { fill: transparent; overflow: visible; } .fa .bg.hover { fill: #f7f7f7; } .c { cy: 12; r: 4; stroke: #d2d2dc; fill: #d2d2dc; } .hr { fill: transparent; stroke: #323c46; stroke-opacity: 0.035; stroke-width: 13; } .dc { stroke: #fa5a3c; fill: #fa5a3c; } .x { stroke: white; stroke-width: 1; } .hl .th { fill: #1b9bff; } .hl .tht {  } .fhl .bg { fill: #e7f4ff; } .fhls .bg {  } .r.hl { stroke: #1b9bff; } .dim { opacity: 0.25; }</style><g class="drawing-wrapper" transform="translate(-75, -195) scale(1,1)"><g class="rs"><path d="M 318 307 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 318 307 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M318 307 L318 307 306 307 M 318 313 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 318 313 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M318 313 L318 313 311 307 318 301 M 318 301 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 318 301 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M306 307 Q 288.25 307, 270.5 296 T 235 285 M 223 285 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 223 285 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M223 285 L223 285 235 285 M229 291 L229 291 229 279" id="h2450294217" class="r"></path><path d="M 318 332 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 318 332 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M318 332 L318 332 306 332 M 318 338 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 318 338 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M318 338 L318 338 311 332 318 326 M 318 326 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 318 326 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M306 332 C -33.60000000000002 332, 75 481, 125 481 M 137 481 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 137 481 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M137 481 L137 481 125 481 M131 487 L131 487 131 475" id="h3838059250" class="r"></path><path d="M 477 357 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 477 357 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M477 357 L477 357 489 357 M 477 363 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 477 363 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M477 363 L477 363 484 357 477 351 M 477 351 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 477 351 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M489 357 Q 505.75 357, 522.5 322 T 556 287 M 568 287 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 568 287 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M568 287 L568 287 556 287 M562 293 L562 293 562 281" id="h2031086128" class="r"></path><path d="M 372 512 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 372 512 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M372 512 L372 512 360 512 M 372 518 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 372 518 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M372 518 L372 518 365 512 372 506 M 372 506 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 372 506 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M360 512 Q 328.75 512, 297.5 398.5 T 235 285 M 223 285 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 223 285 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M223 285 L223 285 235 285 M229 291 L229 291 229 279" id="h2204292749" class="r"></path><path d="M 372 537 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 372 537 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M372 537 L372 537 360 537 M 372 543 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 372 543 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M372 543 L372 543 365 537 372 531 M 372 531 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 372 531 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M360 537 Q 340.25 537, 320.5 509 T 281 481 M 269 481 m -1, 0 a 1,1 0 1,0 2,0 a 1,1 0 1,0 -2,0 M 269 481 m -0.5, 0 a 0.5,0.5 0 1,0 1,0 a 0.5,0.5 0 1,0 -1,0  M269 481 L269 481 281 481 M275 487 L275 487 275 475" id="h1213251098" class="r"></path></g><g class="hrs"></g><g class="ts"><svg width="159" height="200" x="318" y="225" id="h2943076097" class="t"><rect height="40" width="159" class="th"></rect><text x="13" y="25" class="tht">ActivityLog</text><rect height="160" width="159" class="t-bg" y="40"></rect><text x="13" y="61" class="tf pk">ActivityLogID<tspan x="146" class="ty">int</tspan></text><path d="M7,14A2,2 0 0,1 5,12A2,2 0 0,1 7,10A2,2 0 0,1 9,12A2,2 0 0,1 7,14M12.65,10C11.83,7.67 9.61,6 7, 6A6, 6 0 0, 0 1, 12A6, 6 0 0, 0 7, 18C9.61, 18 11.83, 16.33 12.65, 14H17V18H21V14H23V10H12.65Z" class="i pk" transform="translate(113, 48) scale(0.65)"></path><text x="13" y="86" class="tf fk">UserID<tspan x="146" class="ty">int</tspan></text><text x="13" y="111" class="tf fk">GroupID<tspan x="146" class="ty">int</tspan></text><text x="13" y="136" class="tf fk">ActivityTypeID<tspan x="146" class="ty">int</tspan></text><text x="13" y="161" class="tf">ActivityDate<tspan x="146" class="ty">dateTime</tspan></text><text x="13" y="186" class="tf">Stat<tspan x="146" class="ty">int</tspan></text></svg><svg width="126" height="100" x="97" y="228" id="h1537667952" class="t"><rect height="40" width="126" class="th"></rect><text x="13" y="25" class="tht">User</text><rect height="60" width="126" class="t-bg" y="40"></rect><text x="13" y="61" class="tf pk">UserID<tspan x="113" class="ty">int</tspan></text><path d="M7,14A2,2 0 0,1 5,12A2,2 0 0,1 7,10A2,2 0 0,1 9,12A2,2 0 0,1 7,14M12.65,10C11.83,7.67 9.61,6 7, 6A6, 6 0 0, 0 1, 12A6, 6 0 0, 0 7, 18C9.61, 18 11.83, 16.33 12.65, 14H17V18H21V14H23V10H12.65Z" class="i pk" transform="translate(80, 48) scale(0.65)"></path><text x="13" y="86" class="tf">Name<tspan x="113" class="ty">string</tspan></text><path d="M814.545 581.818v-23.273H535.273v-93.09h-46.546v93.09H209.455v139.637H256V605.09h232.727v93.09h46.546v-93.09H768v93.09h46.545V581.819zM674.91 465.455H349.091V139.636h325.818v325.819zM325.82 884.364H139.635V698.182h186.182v186.182z m279.272 0H418.909V698.182h186.182v186.182z m279.273 0H698.182V698.182h186.182v186.182zM395.636 418.909h232.728V186.182H395.636v232.727zM186.182 837.82h93.09v-93.092h-93.09v93.091z m279.273 0h93.09v-93.092h-93.09v93.091z m279.272 0h93.091v-93.092h-93.09v93.091z" class="i ix" transform="translate(67, 75) scale(0.014)"></path></svg><svg width="132" height="225" x="137" y="424" id="h1507136116" class="t"><rect height="40" width="132" class="th"></rect><text x="13" y="25" class="tht">Group</text><rect height="185" width="132" class="t-bg" y="40"></rect><text x="13" y="61" class="tf pk">GroupID<tspan x="119" class="ty">int</tspan></text><path d="M7,14A2,2 0 0,1 5,12A2,2 0 0,1 7,10A2,2 0 0,1 9,12A2,2 0 0,1 7,14M12.65,10C11.83,7.67 9.61,6 7, 6A6, 6 0 0, 0 1, 12A6, 6 0 0, 0 7, 18C9.61, 18 11.83, 16.33 12.65, 14H17V18H21V14H23V10H12.65Z" class="i pk" transform="translate(86, 48) scale(0.65)"></path><text x="13" y="86" class="tf">Name<tspan x="119" class="ty">string</tspan></text><path d="M814.545 581.818v-23.273H535.273v-93.09h-46.546v93.09H209.455v139.637H256V605.09h232.727v93.09h46.546v-93.09H768v93.09h46.545V581.819zM674.91 465.455H349.091V139.636h325.818v325.819zM325.82 884.364H139.635V698.182h186.182v186.182z m279.272 0H418.909V698.182h186.182v186.182z m279.273 0H698.182V698.182h186.182v186.182zM395.636 418.909h232.728V186.182H395.636v232.727zM186.182 837.82h93.09v-93.092h-93.09v93.091z m279.273 0h93.09v-93.092h-93.09v93.091z m279.272 0h93.091v-93.092h-93.09v93.091z" class="i ix" transform="translate(73, 75) scale(0.014)"></path><text x="13" y="111" class="tf">Comment<tspan x="119" class="ty">string</tspan></text><text x="13" y="136" class="tf">ActivePeriod<tspan x="119" class="ty">int</tspan></text><text x="13" y="161" class="tf">QuietPeriod<tspan x="119" class="ty">int</tspan></text><text x="13" y="186" class="tf">ActiveNum<tspan x="119" class="ty">int</tspan></text><text x="13" y="211" class="tf">QuietNum<tspan x="119" class="ty">int</tspan></text></svg><svg width="149" height="200" x="372" y="455" id="h478055793" class="t"><rect height="40" width="149" class="th"></rect><text x="13" y="25" class="tht">UserStat</text><rect height="160" width="149" class="t-bg" y="40"></rect><text x="13" y="61" class="tf fk">UserID<tspan x="136" class="ty">int</tspan></text><text x="13" y="86" class="tf fk">GroupID<tspan x="136" class="ty">int</tspan></text><text x="13" y="111" class="tf">WordCount<tspan x="136" class="ty">int</tspan></text><text x="13" y="136" class="tf">CorrectionCount<tspan x="136" class="ty">int</tspan></text><text x="13" y="161" class="tf">ShareCount<tspan x="136" class="ty">int</tspan></text><text x="13" y="186" class="tf">MemeCount<tspan x="136" class="ty">int</tspan></text></svg><svg width="152" height="125" x="568" y="230" id="h3459333576" class="t"><rect height="40" width="152" class="th"></rect><text x="13" y="25" class="tht">ActivityType</text><rect height="85" width="152" class="t-bg" y="40"></rect><text x="13" y="61" class="tf pk">ActivityTypeID<tspan x="139" class="ty">int</tspan></text><path d="M7,14A2,2 0 0,1 5,12A2,2 0 0,1 7,10A2,2 0 0,1 9,12A2,2 0 0,1 7,14M12.65,10C11.83,7.67 9.61,6 7, 6A6, 6 0 0, 0 1, 12A6, 6 0 0, 0 7, 18C9.61, 18 11.83, 16.33 12.65, 14H17V18H21V14H23V10H12.65Z" class="i pk" transform="translate(106, 48) scale(0.65)"></path><text x="13" y="86" class="tf">Name<tspan x="139" class="ty">string</tspan></text><text x="13" y="111" class="tf">Comment<tspan x="139" class="ty">string</tspan></text></svg></g></g><text class="qdbd-link" x="10" y="20">www.quickdatabasediagrams.com</text></svg>