Before developing the WeChat public platform, first go to the WeChat official website to learn about the general situation here: http://mp.weixin.qq.com/wiki/index.php; after reading it, you have a rough idea and start designing the database. Consider what you can, and add what you haven’t considered later.

1. First is the user part. According to WeChat’s official interface combined with practical applications, the user part has three tables: user table, user information table, and user grouping table. I designed it as follows:

2. After the user has designed it, there is the article part, including: article classification table and article table. The design is as follows:

3. Yes With the user-related table, we can save users. With the table of the article module, we can query articles and return them based on the information entered by the user (return text, graphics, pictures, music, video, voice, etc.), but in actual application, There are situations where we need to specify specific keywords to return specific content, then we need a special specific keyword module, including: keyword table, keyword returned content table, the content table here looks like the above article The tables overlap, but this is not the case. Here is the reply content specified by specific keywords. You can specify to return text, graphics, or other multimedia information. When multimedia information is returned, the content is stored in the file address. The above article table is standard and universal. The article content table is used for user queries. In most cases, graphic information is directly returned. When the graphic information clicks on the link, it is the display address corresponding to this article. It is equivalent to a microsite, so the content table of the article and this keyword is designed separately to make it more convenient. Easy to manage. The design is as follows:

4. Message recording. Record the messages sent by users to facilitate subsequent processing. For example, based on the last event message sent by the user, send it later. When using the same keyword, the content under the corresponding menu is returned, instead of the user sending a message with the corresponding menu option every time; customer service messages provide personalized services based on the recorded information, etc. Including: a record table of messages sent by users and a record table of messages replied to users. With these, the conversation with the user can be completely restored. Here is the process of recording the communication with the user. Specifically, what kind of messages sent by the user need to be returned? The message is determined by the business in the project code. The design is as follows:

5. Custom menu, create and manage the custom menu in the WeChat public platform, the design is as follows:

6. Others are designed based on the actual projects, such as user analysis, access statistics; user points; QR code related, etc., which will not be designed here.

Some of the above designs are not designed into the database, such as the reply message type in the keyword module and the message type in the message record. These are a small number of single options that are fixed (determined by Tencent). For data in the form, we use other methods to implement it in specific projects (fixed static, configuration files, cache, etc.) without designing a database.

The complete design is as follows: (To facilitate screenshots, I dragged the tables of each module to cover them)

Download the script as follows: Create in the database A database named WeChat (defined by myself when designing the database), the script will be OK once executed,

use  weixin -------微信公眾平臺
if exists ( select *  from  sysobjects where name = 'tb_User' and xtype='U') 
drop table tb_User
create table  tb_User------------------------------------------------------------用戶表
(
  ID               int              primary key identity(1,1)       NOT NULL , --主鍵-主鍵
  InId             int              default  0                      NOT NULL , --自有系統(tǒng)的用戶ID
  OpenId           varchar(150)     default ''                      NOT NULL , --微信openid
  Group            int              default  0                      NOT NULL , --分組ID
  NickName         varchar(50)      default ''                      NOT NULL , --昵稱-微信
  CreateTime       datetime         default  getdate()              NOT NULL , --創(chuàng)建時間
  State            int              default  1                      NOT NULL , --狀態(tài)-1為正常
  PreFirst         varchar(150)     default ''                      NOT NULL , --預(yù)留字段1
);


if exists ( select *  from  sysobjects where name = 'tb_Group' and xtype='U') 
drop table tb_Group
create table  tb_Group-----------------------------------------------------------分組表
(
  ID               int              primary key identity(1,1)       NOT NULL , --主鍵-主鍵
  Name             varchar(50)      default ''                      NOT NULL , --名稱-本地
  WeiId            int              default  0                      NOT NULL , --對應(yīng)微信分組ID
  WeiName          varchar(50)      default ''                      NOT NULL , --微信分組名
  Note             varchar(100)     default ''                      NOT NULL , --備注
  CreateTime       datetime         default  getdate()              NOT NULL , --創(chuàng)建時間
  State            int              default  1                      NOT NULL , --狀態(tài)
);


if exists ( select *  from  sysobjects where name = 'tb_UserData' and xtype='U') 
drop table tb_UserData
create table  tb_UserData--------------------------------------------------------用戶資料表
(
  ID               int              primary key identity(1,1)       NOT NULL , --主鍵-主鍵
  UserId           int              default  0                      NOT NULL , --用戶表主鍵
  Sex              int              default  0                      NOT NULL , --性別0未知1男2女
  City             varchar(20)      default ''                      NOT NULL , --城市
  Country          varchar(30)      default ''                      NOT NULL , --國家
  Province         varchar(20)      default ''                      NOT NULL , --省份
  Language         varchar(15)      default ''                      NOT NULL , --語言
  HeadImgUrl       varchar(250)     default ''                      NOT NULL , --用戶頭像
  SubTime          varchar(50)      default ''                      NOT NULL , --最后次關(guān)注時間戳
  CreateTime       datetime         default  getdate()              NOT NULL , --創(chuàng)建時間
  State            int              default  1                      NOT NULL , --狀態(tài)
  PreFirst         varchar(150)     default ''                      NOT NULL , --預(yù)留1
);


if exists ( select *  from  sysobjects where name = 'tb_Article' and xtype='U') 
drop table tb_Article
create table  tb_Article---------------------------------------------------------文章表
(
  ID               int              primary key identity(1,1)       NOT NULL , --主鍵-主鍵
  SortId           int              default  0                      NOT NULL , --類別ID
  ITop             int              default  0                      NOT NULL , --置頂0不1是
  TopBeginTime     datetime         default  getdate()              NOT NULL , --置頂開始時間
  TopEndTime       datetime         default  getdate()              NOT NULL , --置頂結(jié)束時間
  Title            varchar(100)     default ''                      NOT NULL , --標題
  KeyWorld         varchar(150)     default ''                      NOT NULL , --關(guān)鍵字
  Summary          varchar(680)     default ''                      NOT NULL , --簡介//680為微信文字上限左右
  Content          ntext            default ''                      NOT NULL , --內(nèi)容
  Source           varchar(50)      default  '獨家原創(chuàng)'                 NOT NULL , --來源
  CreateTime       datetime         default  getdate()              NOT NULL , --創(chuàng)建時間
  PublishTime      datetime         default  getdate()              NOT NULL , --發(fā)布時間
  AOrder           int              default  99                     NOT NULL , --排序
  State            int              default  1                      NOT NULL , --狀態(tài)
  MinImg           varchar(350)     default ''                      NOT NULL , --縮略圖
);


if exists ( select *  from  sysobjects where name = 'tb_ArtSort' and xtype='U') 
drop table tb_ArtSort
create table  tb_ArtSort---------------------------------------------------------文章分類表
(
  ID               int              primary key identity(1,1)       NOT NULL , --主鍵-主鍵
  Name             varchar(50)      default ''                      NOT NULL , --名稱
  ParentId         int              default  0                      NOT NULL , --父級ID
  IndexLevel       int              default  1                      NOT NULL , --當(dāng)前級別
  SOrder           int              default  99                     NOT NULL , --排序
  State            int              default  1                      NOT NULL , --狀態(tài)
  Note             varchar(150)     default ''                      NOT NULL , --備注
);


if exists ( select *  from  sysobjects where name = 'tb_KeyWord' and xtype='U') 
drop table tb_KeyWord
create table  tb_KeyWord---------------------------------------------------------關(guān)鍵字表
(
  ID               int              primary key identity(1,1)       NOT NULL , --主鍵-主鍵
  Name             varchar(50)      default ''                      NOT NULL , --名稱
  Note             varchar(250)     default ''                      NOT NULL , --備注
  State            int              default  1                      NOT NULL , --狀態(tài)
  CreateTime       datetime         default  getdate()              NOT NULL , --創(chuàng)建時間
  ReType           int              default  1                      NOT NULL , --回復(fù)消息類型1為文本
);


if exists ( select *  from  sysobjects where name = 'tb_KeyContent' and xtype='U') 
drop table tb_KeyContent
create table  tb_KeyContent------------------------------------------------------關(guān)鍵字返回內(nèi)容表
(
  ID               int              primary key identity(1,1)       NOT NULL , --主鍵-主鍵
  Title            varchar(150)     default ''                      NOT NULL , --標題
  Content          varchar(700)     default ''                      NOT NULL , --內(nèi)容
  KeyId            int              default  0                      NOT NULL , --關(guān)鍵字ID
  Type             int              default  1                      NOT NULL , --類型-文本圖文等
  MinImg           varchar(250)     default ''                      NOT NULL , --圖片
  ITop             int              default  0                      NOT NULL , --置頂
  TopBeginTime     datetime         default  getdate()              NOT NULL , --置頂開始時間
  TopEndTime       datetime         default  getdate()              NOT NULL , --置頂結(jié)束時間
  CreateTime       datetime         default  getdate()              NOT NULL , --創(chuàng)建時間
  State            int              default  1                      NOT NULL , --狀態(tài)
  Href             varchar(250)     default  '#'                    NOT NULL , --圖文時點開的鏈接
);


if exists ( select *  from  sysobjects where name = 'tb_UserMsg' and xtype='U') 
drop table tb_UserMsg
create table  tb_UserMsg---------------------------------------------------------用戶消息記錄表
(
  ID               int              primary key identity(1,1)       NOT NULL , --主鍵-主鍵
  MsgType          int              default  1                      NOT NULL , --消息類型文本、事件
  EventId          int              default  1                      NOT NULL , --事件ID//自定義菜單的ID
  Content          varchar(700)     default ''                      NOT NULL , --消息內(nèi)容
  CreateTime       datetime         default  getdate()              NOT NULL , --創(chuàng)建時間
  State            int              default  1                      NOT NULL , --狀態(tài)
  ReState          int              default  0                      NOT NULL , --回復(fù)狀態(tài)
  WeiMsgId         varchar(50)      default ''                      NOT NULL , --微信消息ID
  UserId           int              default  0                      NOT NULL , --用戶表主鍵
);


if exists ( select *  from  sysobjects where name = 'tb_245' and xtype='U') 
drop table tb_245
create table  tb_245-------------------------------------------------------------回復(fù)消息表
(
  ID               int              primary key identity(1,1)       NOT NULL , --主鍵-主鍵
  UserId           int              default  0                      NOT NULL , --用戶表主鍵
  MsgID            int              default  0                      NOT NULL , --消息表主鍵
  ReType           int              default  1                      NOT NULL , --回復(fù)類型//文本圖文
  ReFrom           int              default  1                      NOT NULL , --回復(fù)點//1文章2關(guān)鍵詞
  ReContentId      varchar(80)      default  '0,'                   NOT NULL , --回復(fù)的內(nèi)容ID串
  CreateTime       datetime         default  getdate()              NOT NULL , --回復(fù)記錄時間
);


if exists ( select *  from  sysobjects where name = 'tb_PersonalMenu' and xtype='U') 
drop table tb_PersonalMenu
create table  tb_PersonalMenu----------------------------------------------------自定義菜單表
(
  ID               int              primary key identity(1,1)       NOT NULL , --主鍵-主鍵
  Name             varchar(50)      default ''                      NOT NULL , --名稱
  Type             int              default  1                      NOT NULL , --類型1click2view
  ParentId         int              default  0                      NOT NULL , --父級ID
  IndexLevel       int              default  1                      NOT NULL , --當(dāng)前級別
  LinkUrl          varchar(350)     default  '#'                    NOT NULL , --鏈接地址view時用
  CreateTime       datetime         default  getdate()              NOT NULL , --創(chuàng)建時間
  POrder           int              default  99                     NOT NULL , --排序
  State            int              default  1                      NOT NULL , --狀態(tài)
  Note             varchar(150)     default ''                      NOT NULL , --備注
);

For more asp.net development of WeChat public platform (1) database design related articles, please pay attention to the PHP Chinese website!