- Today we will learn to design a MINI ERP SYSTEM database in MS SQL SERVER Which includes “Person Information table”, “Purchase Order table”, “Machine table”, “Parts table”, “Assembly table”, “Delivery Challan Part RM(RM = Row Material) table” and “Delivery Challan PP(PP = Parts Process)”.
1) Person Information:
- In this Diagram we have a total 6 tables named as “tblPerson”, “tblAddress”, “tblContact”, “tblCompany”, “tblEmployee” and “tblContactType”.
- Each table has some fields which is shown in the Diagram.
- Table “tblPerson” and “tblEmployee” are connected through foreign key PersonID.
- Table “tblPerson” and “tblAddress” are connected through foreign key AddressID.
- Table “tblPerson” and “tblCompany” are connected through foreign key CompanyID.
- Table “tblPerson” and “tblContact” are connected through foreign key PersonID.
- Table “tblContact” and “tblContactType” are connected through foreign key ContactTypeID.
2) Purchase Order:
- In this Diagram we have a total 5 tables named as “tblPO”, “tblDeliveryChallanRM”,
“tblPO_Parts”, “tblCompany” and “tblPerson”.
- Each table has some fields which is shown in the Diagram.
- Table “tblPO” and “tblDeliveryChallanRM” are connected through foreign key PO_ID.
- Table “tblPO” and “tblPO_Parts” are connected through foreign key PO_ID.
- Table “tblPO ” and “tblCompany” are connected through foreign key CompanyID.
- Table “tblPO ” and ” tblPerson” are connected through foreign key PersonID.
- Table “tblDeliveryChallanRM” and “tblCompany” are connected through foreign key CompanyID.
- Table “tblDeliveryChallanRM ” and “tblPerson ” are connected through foreign key PersonID.
- Table “tblCompany ” and “tblPerson ” are connected through foreign key CompanyID.
3) Parts:
- In this Diagram we have a total 3 tables named as “tblParts”, “tblPO_Parts”
And “tblPartsMfgBy”.
- Each table has some fields which is shown in the Diagram.
- Table “tblParts” and “tblPO_Parts” are connected through foreign key PartsID.
- Table “tblParts” and “tblPartsMfgBy” are connected through foreign key PartsID.
4) Machine:
- In this Diagram we have a total 3 tables named as “tblMachine”, “tblMachineAssemblyPartsProcess” And “tblCompany”.
- Each table has some fields which is shown in the Diagram
- Table ” tblMachine ” and ” tblMachineAssemblyPartsProcess ” are connected through foreign key MachineID.
- Table ” tblMachine ” and ” tblCompany ” are connected through foreign key CompanyID.
5) Assembly:
- In this Diagram we have a total 5 tables named as “tblAssembly”, “MachineAssemblyPartsProcessID”, “tblEmployeeAssembly”, “tblEmployeeAssemblyParts”, And “tblAssemblyParts”.
- Each table has some fields which is shown in the Diagram.
- Table ” tblAssembly ” and ” MachineAssemblyPartsProcessID ” are connected through foreign key AssemblyID.
- Table ” tblAssembly ” and ” tblEmployeeAssembly ” are connected through foreign key AssemblyID.
- Table ” tblAssembly ” and ” tblEmployeeAssemblyParts ” are connected through foreign key AssemblyID.
- Table ” tblAssembly ” and ” tblAssemblyParts ” are connected through foreign key AssemblyID.
6) Delivery Challan PP:
- In this Diagram we have a total 4 tables named as “tblDeliveryChallanPP”, “tblDeliveryChallanPP_PartsProcess”, “tblEmployee” And “tblCompany”.
- Each table has some fields which is shown in the Diagram.
- Table ” tblDeliveryChallanPP ” and ” tblDeliveryChallanPP_PartsProcess ” are connected through foreign key DeliveryChallanPPID.
- Table ” tblDeliveryChallanPP ” and ” tblEmployee ” are connected through foreign key ChallanSentByID.
- Table ” tblDeliveryChallanPP ” and ” tblCompany ” are connected through foreign key ChallanSentByCompanyID.
- Table ” tblEmployee ” and ” tblCompany ” are connected through foreign key CompanyID.
7) Delivery Challan Part RM:
- In this Diagram we have a total 4 tables named as ” tblDeliveryChallanRM “, ” tblCompany”, ” tblPerson ” And “tblPO”.
- Each table has some fields which is shown in the Diagram.
- Table ” tblDeliveryChallanRM ” and ” tblCompany” are connected through foreign key ChallanSentByCompanyID.
- Table ” tblDeliveryChallanRM ” and ” tblPerson ” are connected through foreign key ChallanReceivedByID.
- Table ” tblDeliveryChallanRM ” and ” tblPO” are connected through foreign key PO_ID.
- Table ” tblPerson ” and ” tblCompany ” are connected through foreign key CompanyID.
- Table ” tblPerson ” and ” tblPO ” are connected through foreign key PO_MadeByID.
Table ” tblCompany ” and ” tblPO ” are connected through foreign key PO_MadeByCompanyID.
We @ DNG INC always strive to work for futuristic analytical techniques to solve today’s
problem and Derive Next Gen (www.dngsoftwares.com) data solutions.
0 Comments