Shulou(Shulou.com)06/02 Report--

This article mainly explains "what is the method of database design". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the method of database design"?

Database design

1. Why design a database?

Save storage space for data

Ensure the integrity of the data

It is convenient to develop the system according to the database.

two。 Design the database according to the requirements

Sign-in function

Log table (sign-in user, sign-in item number, sign-in time) this field is not necessary, this field is added only to facilitate future expansion, the other two fields are necessary

Project table (project number, project name, creation time) this table is not necessary, but it can be added or not to facilitate future expansion.

Statistical table (item number, total number of punches, consecutive punches, users, time) the fields in this table are all necessary basic fields except for the item number, which is not required.

I only designed three tables to facilitate future expansion. Except for the project table, the other two tables are the most basic and necessary.

First, create a database clockin CHARACTER SET: specify the character set used by the database, utf8 cannot be written as utf-8 COLLATE: specify the collation of the database character set, and the default collation of utf8 is

Utf8_general_ci (viewed via show character set) drop database if EXISTS clockin; create database clockin CHARACTER SET utf8 COLLATE utf8_general_ci

The sql statement is as follows

DROP TABLE IF EXISTS `clockin_ count`; CREATE TABLE `clockin_ count` (`id` int (11) NOT NULL AUTO_INCREMENT, `pid` int (11) DEFAULT NULL COMMENT 'clock-in items', `sum` int (11) DEFAULT NULL COMMENT 'total clock-in', `cloop` int (11) DEFAULT NULL COMMENT 'clock-in series', `name` varchar (25) COLLATE utf8_bin DEFAULT NULL COMMENT 'puncher', `dtime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; DROP TABLE IF EXISTS `clockin_ log` CREATE TABLE `clockin_ log` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (25) COLLATE utf8_bin DEFAULT NULL COMMENT 'puncher', `pid` int (11) DEFAULT NULL COMMENT 'punch project', `dtime`datetime DEFAULT NULL COMMENT 'punch time', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; DROP TABLE IF EXISTS `clockin_ project` CREATE TABLE `clockin_ project` (`id` int (11) NOT NULL AUTO_INCREMENT, `pid` int (11) NOT NULL, `project` varchar (25) COLLATE utf8_bin NOT NULL, `dtime`project` datetime DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

3. Understand clearly the mapping relationship between data tables

Clockin_log: log table, which stores users' daily sign-in log information. The data update rate of the table is relatively high. It is hereby proposed to be used as a separate table.

Clockin_project: project list, clocking in on what items is not unnecessary, but is added to facilitate future expansion.

Clockin_count: statistical tables, statistical log tables of users' sign-in information, the data is relatively less, the extraction is more intuitive, and the query data is used more frequently

Realization idea

First of all, judge whether the project exists before you can clock in, then judge whether you have signed in today, insert the data if you did not sign in today, and then judge whether you signed in yesterday.

If you clock in yesterday, the number of consecutive days will be increased by one. If you do not clock in yesterday, the number of consecutive days will be set to one, and the total number of days will be added to one. The mind map is as follows:

Here is mainly the writing of sql statements. I mainly use the LEFT syntax in sql, which corresponds to substr () in oracle, mainly to judge whether to sign in today or yesterday.

Syntax: = = LEFT (ARG,LENGTH) = = ARG source data, LENGTH string, ARG can be CHAR or BINARY STRING

For example: left ('12345)-- > 12

SELECT * FROM clockin_log where name = # {name} and pid = # {pid} and LEFT (dtime,10) = LEFT (NOW (), 10) SELECT * FROM clockin_log where name = # {name} and pid = # {pid} and LEFT (dtime,10) = LEFT (# {dtime}, 10) 2020-10-10 is obtained from 2020-10-10T10:30:51

Main code

I use MybatisPlus here. For specific use, please refer to

The Result here is that I defined a result set that contains code-- status code, msg-- return message, data-- data information.

/ / name user name pid project number public Result clock (String name,String pid) {/ / the returned data type Result result = new Result (); / / get yesterday's date Calendar cal= Calendar.getInstance (); cal.add (Calendar.DATE,-1); Date yesterday=cal.getTime (); / / determine whether to clock in today Boolean isexit = clockinLogService. SelectClockinIsexit (name, Integer.parseInt (pid)); LambdaQueryWrapper wrapper = new LambdaQueryWrapper (); wrapper.eq (ClockinCount::getName,name) .eq (ClockinCount::getPid,Integer.parseInt (pid)); ClockinCount one = clockinCountService.getOne (wrapper); LambdaQueryWrapper wrapper1 = new LambdaQueryWrapper (); wrapper1.eq (ClockinProject::getPid,Integer.parseInt (pid)); ClockinProject project = clockinProjectService.getOne (wrapper1) / / whether there is an if (! StringUtils.isEmpty (project)) {if (isexit) {/ / judge whether to sign in today result.setRetCode (Result.ERROR); result.setRetMsg ("signed in today"); result.setRowData (one); return result } else {/ / record sign-in information ClockinLog clockinLog = new ClockinLog (); clockinLog.setName (name) .setPid (Integer.parseInt (pid)) .setDtime (LocalDateTime.now ()); boolean save = clockinLogService.save (clockinLog) If (save) {/ / whether the data was inserted successfully or not Boolean yesterdayIsexit = clockinLogService. SelectClockinYesterdayIsexit (name, Integer.parseInt (pid), yesterday); System.err.println ("yesterdayIsexit- >" + yesterdayIsexit); if (yesterdayIsexit) {/ / judge whether System.err.println signed in yesterday ("yesterday") / / update the number of punches ClockinCount cc = new ClockinCount (); LambdaUpdateWrapper updateWrapper = new LambdaUpdateWrapper (); updateWrapper.eq (ClockinCount::getPid,Integer.parseInt (pid)) .eq (ClockinCount::getName, name) Cc.setSum (one.getSum () + 1) .setCloop (one.getCloop () + 1); clockinCountService.update (cc,updateWrapper) } else {if (! StringUtils.isEmpty (one)) {/ / whether users exist / / update the number of punches in the statistical table ClockinCount cc = new ClockinCount (); LambdaUpdateWrapper updateWrapper = new LambdaUpdateWrapper () UpdateWrapper.eq (ClockinCount::getPid,Integer.parseInt (pid)) .eq (ClockinCount::getName, name); cc.setSum (one.getSum () + 1) .setCloop (1); clockinCountService.update (cc,updateWrapper) } else {/ / insert data information ClockinCount cc = new ClockinCount (); cc.setCloop (1) .setName (name) .setPid (Integer.parseInt (pid)) .setSum (1) .setDtime (LocalDateTime.now ()) ClockinCountService.save (cc);}} one = clockinCountService.getOne (wrapper); result.setRetCode (Result.SUCCESS); result.setRetMsg ("success"); result.setRowData (one) Return result;} else {result.setRetCode (Result.ERROR); result.setRetMsg ("failure"); result.setRowData ("sign-in item does not exist");} return null At this point, I believe you have a deeper understanding of "what is the method of database design". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.