029 Medical Project-Module III: Drug supplier Catalogue module--Analysis of data Model (build table)

This article mainly analyzes the structure of database tables, and the relationship between tables and tables.

As follows:

Analysis of suppliers:

suppliers need to add their own drug information to the supplier's drug catalogue, the drug catalog exists in the drug supplier to supply these drugs, the hospital according to suppliers to supply drugs to purchase.

Functional Analysis:

1: Supplier Add Medicine Catalogue

Stating that the supplier is to supply these drugs

Insert a piece of data into the supplier's Drug catalog table

2: supplier removal of medicines catalogue

The supplier is no longer available to delete records from the supplier's drug catalogue and delete records based on supplier ID and drug information ID

List of suppliers ' medicines :

Id: Primary Key

Supplier ID: FOREIGN key, referring to the primary key of the Supplier Unit table

Drug Information ID: foreign key, primary key of drug information table

Unique constraint: ( supplier id+ drug Information ID)

Analysis of drug surveillance:

The supervisory unit may control the drug supplied by the supplier, allow the supplier to supply it, or not allow the supplier to supply it.

Principle: For different business analyses, it is preferable to create a table separately.

Functional analysis;

Add supplier drug information to supplier drug Catalog control sheet

said: The supervisory authority shall control the drug supplied by the supplier

To modify the supply status according to the supplier and the drug (to allow the supplier or not to supply the supplier)

Representation: Supervisory Unit Execution control

Remove the supplier drug information from the supplier's Drug catalog control form:

said: The supervisory unit no longer controls the supply of drugs to suppliers

Supplier Drug Catalogue control Table :

Id: Primary Key

Supplier ID: FOREIGN key, referring to the primary key of the Supplier Unit table

Drug Information ID: foreign key, primary key of drug information table

Delivery Status: (1, Normal, 2, suspension of delivery)

This is the analysis of two characters:

Let's take a look at how the analysis is done to optimize:

The supervisory unit controls the supplier and is not affected by the supplier's drug catalogue, which can control the supplier's medicines at any time.

This control business has no problem with the functional analysis process, but it does not make much sense because the supplier does not supply the medicine and the hospital cannot procure it.

Add a record to the supplier's Drug catalog control table while the supplier adds the drug to its own drug catalogue.

Supplier Drug Catalogue Maintenance function Analysis:

Supplier Add Medicine Catalogue

Stating that the supplier is to supply these drugs

Insert a piece of data into the supplier's Drug catalog table

At the same time, the Supplier Drug Catalog Control table adds a record

Supplier to remove drug catalogue

The supplier is no longer available to delete records from the supplier's drug catalogue and delete records based on supplier ID and drug information ID

Supplier Drug Catalogue control function analysis;

Add supplier drug information to supplier drug Catalog control sheet

said: The supervisory authority shall control the drug supplied by the supplier

With optimizations, this addition can be done without

To modify the supply status according to the supplier and the drug (to allow the supplier or not to supply the supplier)

Representation: Supervisory Unit Execution control

Remove the supplier drug information from the supplier's Drug Catalog control form:

said: The supervisory unit no longer controls the supply of drugs to suppliers

This feature is removed, regardless of whether the supplier's drugs are controlled, are kept in the database.

Is the amount of data large? Data volume = Total number of drug catalogs * number of suppliers

Optimization plots:

The relationship between the two tables:

Next Design Table:

There is a business relationship: suppliers are asked to inquire about the availability of pharmaceuticals in the catalogue. Query method: Through the related Supplier Drug Catalog Control Table query, through the internal link to check the Supplier Drug catalog control status. --the data in the GYSYPML table is present in the Gysypml_control table--Query with UNIQUE Constraint association (only one can be queried)--Query record number of records is equal to GYSYPML table--because GYSYPML table main query table Select *   fromGYSYPML, Gysypml_controlwhereGysypml.ypxxid=Gysypml_control.ypxxid andGysypml.usergysid=Gysypml_control.usergysid--through subqueries   SelectGYSYPML.*,                 (SelectControl fromGysypml_controlwhereGysypml.ypxxid=Gysypml_control.ypxxid andGysypml.usergysid=gysypml_control.usergysid) Control fromGYSYPML

Make a summary of this database:

Two tables:

List of suppliers ' Medicines: GYSYPML:

Records the drug information supplied by the supplier, if the drug is supplied by the supplier, if there is no medicine in the table, the supplier does not supply the drug

Hospital procurement of drugs from this table to find suppliers to supply drugs to find that the supplier to supply the drug, but also to meet the supervision unit to allow suppliers to supply. (How do I check for availability?) Check Gysypml_control by supplier ID and drug ID to find out the status of delivery)

Medicines that can be procured by the hospital: in the presence of GYSYPML and in the Gysypml_control table, the availability is allowed.

Supplier Drug Catalogue Control Table: Gysypml_control

The control status of the supplier supplied by the supervising unit is recorded.

Two table operations should not affect each other (because two tables belong to different business);

Two tables are optimized: Inserts a record into the Gysypml_control table while inserting records into the GYSYPML.

Supplier Drug Catalogue Maintenance:

For GYSYPML operations:

Add a record stating that the supplier will supply the medicine.

Insert a record into the Gysypml_control table

Delete the record stating that the supplier is no longer supplying the drug.

from Gysypml_control table Delete a record?? no need to delete

Supplier Drug Catalog Control:

For Gysypml_control operations:

Update delivery status according to Supplier ID and drug ID (1: Normal, 2: Paused)

029 Medical Project-Module III: Drug supplier Catalogue module--Analysis of data Model (build table)