Mysql creates a general-purpose device management information system database and mysql general-purpose devices
The following figure shows how to create a database for the information system of general device management. For more information, see the following.
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;
Download database resources
Http://pan.baidu.com/s/1skFeGVj
The above content is all about creating a general-purpose device management information system database for Mysql. I hope it will help you!
Articles you may be interested in:
- Use IE to remotely create a Mysql database
- Why does php select mysql as the database? Mysql user Creation Method
- Code for creating a database and a database table with MySQL
- Use phpMyadmin to create a Mysql database and an independent database account
- Basic database creation for Mysql
- Mysql startup and database creation methods [Image and text]
- MySQL Study Notes 2: Basic Database Operations (create, delete, and view)
- In-depth analysis of MySQL Database Engine introduction, differences, creation and performance testing
- How to create and delete database commands and related PHP scripts in MySQL
- Tutorial on creating a MySQL database using the mysql_query () function of PHP