Microservice architecture case (03): introduction to database selection, business data planning and design

Keywords: Java Database MySQL github HBase

Source code: GitHub point here || GitEE point here

Update progress (6 sections in total):

01: brief introduction of project technology selection and schematic description of structure

02: business architecture design, system hierarchical management

03: database selection, business data design and planning

I. database selection

1. Database classification

Database type	Common databases
Relational type	MySQL, Oracle, DB2, SQL server, etc.
Non relational	Hbase, Redis, mongodb, etc.
Row store	MySQL, Oracle, DB2, SQL server, etc.
Column store	Hbase, ClickHouse, etc.
Distributed storage	Cassandra, Hbase, mongodb, etc.
Key value storage	Memcached, Redis, MemcacheDB, etc.
Graphic storage	Neo4J, tiger graph, etc.
Document storage	MongoDB, CouchDB, etc.

2. Database selection

Based on the specific application environment, select the most suitable database, establish data storage mode, so that it can effectively store data to meet the application needs of various users. For example, for a common business database, MySQL is selected when the data volume is not large; elastic search can be used when there are frequent search operations; a large number of hot data can be used in the system, such as a common cache database.

3. Microservice database

A key point of microservice architecture is database design and planning. The basic principle is that each service has its own database, and only microservice itself can access this database. If other services want to access, they can only operate by calling the external interface provided by the service, which can compress the database operation interface, provide support in troubleshooting and performance optimization, and make the system framework more organized. This mode is illustrated as follows:

Microservice C operates database A through microservice A or database B through microservice B.

II. Planning business database

1. Overall Division

It mainly uses three kinds of data storage: MySQL (divided into three business databases), elastic search (single), and redis (single).

2. User library

(User data) stores User related data structures, such as User information, Token, operation log, etc.

CREATE TABLE `hc_user_base` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
  `user_name` varchar(50) DEFAULT NULL COMMENT 'User name',
  `pass_word` varchar(300) DEFAULT NULL COMMENT 'Encryption cipher',
  `phone` varchar(30) DEFAULT NULL COMMENT 'Cell-phone number',
  `email` varchar(50) DEFAULT NULL COMMENT 'mailbox',
  `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
  `update_time` datetime DEFAULT NULL COMMENT 'Update time',
  `state` int(1) DEFAULT '0' COMMENT 'state:0 available,1 Prohibit',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User table';

3. Management system library

(admin data) stores the supporting database of the background microservice management system, such as timer, administrator authority, configuration dictionary, etc.

-- managers
CREATE TABLE `hc_admin_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
  `user_name` varchar(50) DEFAULT NULL COMMENT 'User name',
  `pass_word` varchar(300) DEFAULT NULL COMMENT 'Encryption cipher',
  `phone` varchar(30) DEFAULT NULL COMMENT 'Cell-phone number',
  `email` varchar(50) DEFAULT NULL COMMENT 'mailbox',
  `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
  `update_time` datetime DEFAULT NULL COMMENT 'Update time',
  `state` int(1) DEFAULT '0' COMMENT 'state:0 available,1 Prohibit',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Manage user tables';

-- Roles and permissions list
CREATE TABLE `hc_role_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
  `role_name` varchar(64) DEFAULT NULL COMMENT 'Role name',
  `role_auth` varchar(64) DEFAULT NULL COMMENT 'Role permissions',
  `create_time` datetime DEFAULT NULL COMMENT 'Adding time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='System role table';

-- Timer list
CREATE TABLE `schedule_job` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'task id',
  `bean_name` varchar(200) DEFAULT NULL COMMENT 'spring bean Name',
  `params` varchar(2000) DEFAULT NULL COMMENT 'parameter',
  `cron_expression` varchar(100) DEFAULT NULL COMMENT 'cron Expression',
  `status` tinyint(4) DEFAULT NULL COMMENT 'Task status 0: normal 1: paused',
  `remark` varchar(255) DEFAULT NULL COMMENT 'Remarks',
  `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Timing task';

4. Data analysis library

(report data) stores the archived reports and analysis results. The case mainly demonstrates the analysis of the user's search behavior and stores it in the report library.

-- Book search record
CREATE TABLE `hc_search_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
  `user_id` int(11) DEFAULT NULL COMMENT 'user ID',
  `book_id` int(11) DEFAULT NULL COMMENT 'book ID',
  `book_name` varchar(100) DEFAULT NULL COMMENT 'Titles of books',
  `search_time` datetime DEFAULT NULL COMMENT 'Search time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Records of books searched';

-- Keyword search record
CREATE TABLE `hc_search_key_word` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
  `user_id` int(11) DEFAULT NULL COMMENT 'user ID',
  `key_word` varchar(50) DEFAULT NULL COMMENT 'Key word',
  `search_num` int(11) DEFAULT NULL COMMENT 'Search times',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Keyword search record';

5. Search engine library

(ES data) stores the user's search data, which can be dynamically and real-time imported to es service based on MySQL.

-- Book search information table
CREATE TABLE `hc_book_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
  `book_name` varchar(100) DEFAULT NULL COMMENT 'Titles of books',
  `book_author` varchar(100) NOT NULL DEFAULT '0' COMMENT 'author',
  `book_desc` varchar(200) DEFAULT NULL COMMENT 'brief introduction',
  `book_press` varchar(100) NOT NULL DEFAULT '0' COMMENT 'Press',
  `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
  `update_time` datetime DEFAULT NULL COMMENT 'Update time',
  `state` int(1) DEFAULT '0' COMMENT 'Status: 0 available,1 delete',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Book information sheet';

III. summary of database design

Database design is a core point of microservice design. The basic principle is that each microservice has its own database, and only the microservice itself can access this database. In the microservice architecture, database design should first meet the needs of users, facilitate maintenance and expansion, have good read-write performance, and also help developers understand and manage the system.

IV. source code address

GitHub·address
https://github.com/cicadasmile/husky-spring-cloud
GitEE·address
https://gitee.com/cicadasmile/husky-spring-cloud

Posted by nerotic on Sat, 02 Nov 2019 00:47:00 -0700

Programmer Group