Create a device management information system database with Mysql (illustration)

Create a device management information system database with Mysql (illustration)
General Equipment Management Information System database
Device table: id, name, type, model, operation date, purchase date, manufacturing unit, quantity, measurement unit, use department, Installation
Location, product image, technical data, remarks;
Defect table: id, device id, defect description, handling status, handling personnel;
Accident table: id, device id, accident description, handling status, handling personnel;
Service Category Table: id, category name, service content, cycle (days)
Device Category Table: id, category name;
Department table: id, Department name;
Device status table: id, status name
Device status: the status of the specified device. The status data includes online, storage, idle, decommission, waiting for repair, and backup.
Maintenance Personnel table: id, name, department, position, title;
Spare Parts table: id, name, quantity, measurement unit, usage, existing inventory, minimum inventory;
Tool table: id, name, quantity, measurement unit, usage;
Service record table: id, service category id, device id, service personnel, service status, service time, and materials consumed;

Device Inspection table: id, device id, check date, inspection personnel, check status, check method;

create database devmng;show databases;

 

create table Equipment(   equipmentID          int not null auto_increment,   devtypeID            int,   devname              varchar(50),   modnum               varchar(50),   touyundate           date,   gourudate            date,   zhidaodw             varchar(100),   quantity             int,   unit                 varchar(8),   useDepartment        varchar(50),   storePlace           varchar(80),   picture              varchar(256),   technicalData        varchar(256),   remark               varchar(256),   primary key (equipmentID));

 

 

create table devbug(   bugID          int not null auto_increment,   devid          int,   bugcontent     varchar(256),   chuliqk        varchar(100),   chuliren       varchar(80),   primary key (bugID));create table devfault(   faultID          int not null auto_increment,   devid            int,   faultcontent     varchar(256),   chuliqk          varchar(100),   chuliren         varchar(80),   primary key (faultID));

 

 

create table repairtype(   rtID             int not null auto_increment,   rtname           varchar(24),   rtcontent        varchar(256),   cycle            int,   primary key (rtID));create table devtype(   dtID             int not null auto_increment,   dtname           varchar(36),   primary key (dtID));create table part(   ptID             int not null auto_increment,   partname         varchar(36),   primary key (ptID));

 

 

Create table devstatus (dsID int not null auto_increment, dsname varchar (12), primary key (dsID); insert into devstatus values (1, "online "); insert into devstatus values (2, "ARCHIVE"); insert into devstatus values (3, "idle"); insert into devstatus values (4, "decommission "); insert into devstatus values (5, "waiting for Repair"); insert into devstatus values (6, "standby ");

 

 

create table repairpl(   rpID             int not null auto_increment,   ptid             int,   rpname           varchar(24),   zhiwu            varchar(36),   zhich            varchar(36),   primary key (rpID));create table bpbj(   bbID             int not null auto_increment,   bbname           varchar(36),   num              int,   unit             varchar(8),   purpose          varchar(256),   xianyounum       int,   minnum           int,   primary key (bbID));

 

Note: use is a keyword. It cannot be used as a field name;

 

create table tools(   tID              int not null auto_increment,   tname            varchar(36),   num              int,   unit             varchar(8),   purpose          varchar(256),   primary key (tID));create table rprecords(   rrID             int not null auto_increment,   rtID             int,   equipmentID      int,   rpren            varchar(100),   rpcontent        varchar(300),   rpdate           date,   wuliao           varchar(150),   primary key (rrID));

 

 

create table checkrecords(   crID             int not null auto_increment,   equipmentID      int,   checkdate        date,   checkren         varchar(80),   checkcontent     varchar(100),   checktype        varchar(36),   primary key (crID));ALTER TABLE Equipment ADD statusID int;

 

Adds a device Status column to the device table;