Invoicing System Database Design Report

Invoicing System Database Design Report

Product management functions

1. Basic information table of product types: (add, delete, modify, and so on)
Field name type width primary key no foreign key no
Type ID integer Yes No
Type name string 20 no
Upper-level ID integer No Yes
Upper-level ID integer No Yes
Note No

2 Basic information table of warehouse receiving type: (preset)
Field name type width primary key no foreign key no
Type ID integer Yes No
Type name string 20 no
Note No
Type: purchase warehouse receiving, return warehouse receiving, transfer warehouse receiving, other warehouse receiving

3. Basic information table of warehouse picking types: (preset)
Field name type width primary key no foreign key no
Type ID integer Yes No
Type name string 20 no
Note No
Types: Retail warehouse picking, wholesale warehouse picking, transfer warehouse picking, inventory warehouse picking, and other warehouse picking

4 features: Product basic information catalog (add, delete, modify, and so on)

4.1 product basic information table:
Field name type width primary key no foreign key no
Product NO. String 20 Yes No
Product Name string 50 No, idx No
Barcode string 20 no, idx No
Component unit string 20 no
Product Specification string 20 no
Product Type ID integer No Yes
Final price unit price currency 20 no
Current retail price currency
Wholesale Price currency

4.2 product inventory table :? (Added, deleted, and modified)
Because the same product number may have different validity dates (shelf life), the form of the inventory table uses two primary keywords: product number and validity date.
Field name type width primary key no foreign key no
Product NO. String 20 Yes No
Valid date string 50 Yes No
Number integer No
Warehouse ID integer No Yes
Shelf ID integer No Yes

5 function: warehouse receiving application (including warehouse receiving for purchase, warehouse receiving for return, warehouse receiving for transfer, and other warehouse receiving types)

5.1 warehouse receiving single master table:
Field name type width primary key no foreign key no
Warehouse receiving Ticket No. String 20 Yes No
Warehouse receiving type ID integer No Yes
Warehouse Receiving date no
Warehouse ID integer No Yes
Supplier ID string 20 No Yes
Receiving operator ID string 20 No Yes
Full acceptance no logic No
Acceptance date
Acceptance number string 10 No Yes
Warehouse receiving review no logic No
Date of warehouse receiving review
Database reviewer No. String 10 No Yes
Payment Method ID integer No Yes
Payment already paid no logic No
Payment Date
Payer ID string
Payment audit no logic no
Payment review date
Payment reviewer ID string 10 No Yes
Source database ID integer No Yes
Remarks
The source database is used for database transfer, and other databases are not used for Database Import.

5.2 warehouse receiving ticket list:
Field name type width primary key no foreign key no
Warehouse receiving ticket string 20 Yes
Single sequential ID integer Yes No
Product No.: 20 no, idx Yes
Product effective date no
Number integer No
Price/unit price currency no
Acceptance no logic No

6. Warehouse picking: Sales and warehouse picking are divided into retail (cash) and wholesale (possibly in arrears)
6.1 warehouse picking table information: (including sales warehouse picking, warehouse transferring, return warehouse picking, other warehouse picking and other types)
Field name type width primary key no foreign key no
Warehouse picking Ticket No. String 20 Yes No
Warehouse picking type ID integer No Yes
Warehouse picking date no
Warehouse ID integer No Yes
Supplier ID string 20 No Yes
Outbound operator ID string 20 No Yes
Full check no logic no
Date of validation
Acceptance number string 10 No Yes
Outbound review no logic No
Date of warehouse picking Review
Warehouse reviewer No. String 10 No Yes
Receipt method ID integer No Yes
Payment received Logic
Date of receipt
Recipient id string
Receipt review Logic
Receipt review date
Receipt reviewer ID string
Target library ID integer No Yes
Remarks
The target database is used for database transfer, but not for other databases.

6.2 sales order list:
Field name type width primary key no foreign key no
Warehouse picking ticket string 20 Yes
Single sequential ID integer Yes No
Product No.: 20 no, idx Yes
Product effective date no
Number integer No
Price/unit price currency no
This test does not have logic No

7 function: Inventory alarm, that is, alarm when there are fewer than a product.
7.1 alert table: (omitted)

Customer Management
1. Basic information table of the customer (add, delete, modify, and so on)
Field name type width primary key no foreign key no
Customer ID string 10 Yes No
Customer name string 50 no
Customer address 1 remarks No
Customer address 2 remarks No
Customer phone 1 string 20 no
Customer Phone 2 string 20 no
Currency of the customer's deposit amount
Remarks
Suspend logic?

Supplier Management
1. Supplier basic information table: (add, delete, modify, and so on)
Field name type width primary key no foreign key no
Supplier ID string 10 Yes No
Supplier name string 50 no
Supplier address 1 remarks No
Supplier address 2 remarks No
Supplier phone 1 string 20 no
Supplier Phone 2 string 20 no
Remarks
Suspend logic?

Operator Management
1. Basic operator information (added, deleted, and modified)
Field name type width primary key no foreign key no
Operator ID string 10 Yes No
Operator name string 50 no
Address 1 remarks No
Address 2 remarks No
Phone 1 string 20 no
Phone 2 string 20 no
Account amount currency
Remarks
Suspend logic?
The operator may have company funds, so a fund account is set up.

2. Permission basic information table (preset)
Field name type width primary key no foreign key no
Permission ID integer Yes No
Permission name string 20 no
Remarks

3. Operator permission table (added, deleted, and modified)
Field name type width primary key no foreign key no
Operator ID string 10 No Yes
Permission ID string 50 no

4. Operator operation logs (added, deleted, and modified)
Field name type width primary key no foreign key no
Operator No. Integer Yes No
Date and Time string 20 no
Operation string
Operation table string

Fund management
1. fund internal transfer table (for addition, deletion, and modification)
Field name type width primary key no foreign key no
Payer ID string 10 No Yes
Payer ID string 10 No Yes
Amount currency no
Occurrence date no
Remarks