Learn about Entity Relationship Diagram
We have to create an ERD diagram for our new project which is used for Enterprise Resource Planning System. There we have to have a sharp knowledge on ERD concepts because it's going to be a business analyst role with me. So I learn about them from the beginning.
According to my point of view, it will be more helpful for DBMS learners from the beginning. I used (
http://www.tutorialspoint.com/dbms/index.htm) for leaning about DBMS concepts with ERD diagram concepts also.
After improving my knowledge on ERD diagrams, I created the ERD diagram for our project by using Visual Paradigm tool. There is a free trial version for 30 days and by using it we draw the ERD after drawing the finalized rough diagram in a excel sheet. Actually it is more easy to draw the ERD diagram roughly at the beginning and we have improve it day by day or time to time when we need changes on ERD. After finalizing the ERD diagram with regard to our customer needs, we can draw the finalized ERD by using an tool like this.
ERD drawing Tool - (
http://www.visual-paradigm.com/)
After drawing the finalized ERD, I was asked to create a report that would be nicely describe the ERD diagram. The description would have a technical description and also non technical description what is it in the system. A part of description would be as follows.
ERD Diagram for ERP Package
The following is the proposed ERD diagram for ERP package
which is cloud based system. There we can see 10 tables. The detailed
review about proposed ERD will be mentioned in the below.
Table name
|
Description
|
Attributes
|
Description
|
Relationship
|
tblCOA
|
This is the table for Chart of Accounts.
|
·
Fld_COAId
·
Fld_CateLable
·
Fld_NameLable
|
·
Fld_COAId is the primary key of the table.
·
Variable characters of all attributes are
varchar.
|
·
Fld_COAId is Composite key of tblComCOA.
·
Fld_COAId is foreign key of tblglname.
|
tblComCOA
|
This is the table for company chart of accounts.
|
·
Fld_COAId
·
Fld_COMID
·
Fld_CateLAble
·
Fld_NameLable
·
Fld_NotAllowMinus
·
Fld_Filter
|
·
Fld_COAID & Fld_COMID are composite key.
·
Variable characters of all attributes instead
of fld_NotAllowMinus are varchar.
·
Fld_NotAllowMinus character is from decimal.
|
·
Fld_COMID is Primary key of tbl_COM.
·
Fld_COAID is the foreign key of tblglname.
|
tblCOM
|
This is the table for company details.
|
·
Fld_COMId
·
Fld_RegNo
·
Fld_MonthEndDate
·
Fld_YearEndDate
·
Fld_ComNameLable
·
Fld_AddressLable
|
·
All the variable characters are varchar
without fld_MonthEndDate & YearEndDate.
·
Fld_COMId is the primary key.
|
Fld_ComId is the foreign key of tblComCOA.
|
tblLable
|
This is the table for Labeling.
|
·
Fld_LableId
·
Fld_Name1
·
Fld_name2
·
Fld_Name3
|
·
Fld_LableId is the primary key.
·
All the variable characters are from varchar.
|
|
Tblglname
|
This is the table for categories.
|
·
Fld_GLNameID
·
Fld_COAId
·
Fld_GLNameLable
·
Fld_ComNameLAble
·
Fld_COACateLable
·
Fld_COANameLable
·
Fld_Filter
|
·
Fld_GLNameID is the primary key.
·
All the
variable characters are from varchar for all attributes.
|
·
Fld_COAId is a foreign key.
·
Fld_GLNameID is a foreign key od tblglaccount.
|
tblBranch
|
This table is for branch details.
|
·
Fld_BRID
·
Fld_BRNAmeLAble
·
Fld_BRAddressLable
|
·
Fld_BRID is the primary key of the table.
·
All the variable characters are from varchar
for all attributes.
|
·
Fld_BRID is a foreign key for tblglaccount.
|
tblTraLable
|
This table is for transaction labeling.
|
·
Fld_TransactionID
·
Fld_LineNo
·
Fld_Name1
·
Fld_name2
·
Fld_Name3
|
·
Fld_TransactionID is the primary key.
·
All the variable characters are from varchar
instead of fld_LineNo.
·
Fld_LineNo is from the variable character of
float.
|
·
Fld_Transaction ID is a composite key for
tbltransactions.
|
Tbl_Member
|
This is the table for insert member details
|
·
Fld_MemNo
·
Fld_TypeId
·
Fld_NameLable
·
Fld_InitialLable
·
Fld_TitleLable
·
Fld_AddressLable
·
Fld_DOB
·
Fld_HomeNo
·
Fld_JoinDate
·
Fld_Active
|
·
Fld_MemNo is the primary key of the table.
·
All the variable characters of attributes are
from varchar without fld_DOB and fle_JoinDate.
|
·
Fld_MemNo is the foreign key of
tblpersonalaccount.
|
tblTransactions
|
This table is for inserting transaction details
|
·
Fld_TransNo
·
Fld_TransYear
·
Fld_AutoNo
·
Fld_AccountNo
·
Fld_CusNo
·
Fld_GLAccountID
·
Fld_BrID
·
Fld_TrDate
·
Fld_UpdDate
·
Fld_UpdTime
·
Fld_Debit
·
Fld_Credit
·
Fld_Balance
·
Fld_Interest
·
Fld_Penalty
·
Fld_Cheq
·
Fld_CheqDate
·
Fld_TrType
·
Fld_PbLine
·
Fld_PbPage
·
Fld_Print
·
Fld_Upd
·
Fld_UserId
·
Fld_CashbookID
·
Fld_PbNo
·
Fld_BatchMode
·
Fld_NoteLable
·
Fld_NameLable
·
Fld_addressLable
|
·
Fld_TransnNo & fld_TransYear is the
composite key of the table.
·
Fld_AutoNo is from float character.
·
Fld_TrDate, fld_UpdDate, fld_UpdTime &
fld_CheqDate are from Time Stamp variable character.
·
Fld_Debit,
Fld_Credit , Fld_Balance ,
Fld_Interest , Fld_Penalty, fld_PbLine & fld_PbPage, fld_PbNo &
fld_BatchMode are from the variable character of double.
·
All the other attributes are from varchar
variable character.
|
·
Fld_TransNo is the foreign key of
fld_TransactionID of tbl_TraLable.
·
Fld_AccountNo is the foreign key of
tblpresonalaccount table.
|
Tblpersonalaccount
|
This table is for inserting personal account details.
|
·
Fld_AccountNo
·
Fld_GLAccountID
·
Fld_MemNo
·
Fld_BrID
·
Fld_InteID
·
Fld_PenID
·
Fld_FundID
·
Fld_OpenDate
·
Fld_Time
·
Fld_DueDate
·
Fld_Approved
·
Fld_Capital
·
Fld_Balance
·
Fld_Hold
·
Fld_MinBalance
·
Fld_Period
·
Fld_InteRate
·
Fld_PenRate
·
Fld_FundRate
·
Fld_Interst
·
Fld_Penalty
·
Fld_Fund
·
Fld_Acc_Type
·
Ald_Acc_Type2
·
Fld_InteType
·
Fld_PenType
·
Fld_PassType
·
Fld_installment
·
Fld_DueInte
·
Fld_PayDate
·
Fld_PBType
·
Fld_PBLine
·
Fld_PBPage
·
Fld_Renew
·
Fld_Act
·
Fld_Note
·
Fld_StdOrders
·
Fld_TraAccount
·
Fld_DedAmount
·
Fld_ReneType
·
Fld_OpeDeb
·
Fld_OpeCre
·
Fld_Filter
·
Fld_Name Lable
·
Fld_InitialLable
·
Fld_TitleLable
·
Fld_AddressLable.
|
·
Fld_AccountNo is the primary key.
·
Fld_OpenDate, Fld_Time, Fld_DueDate,
fld_PayDate are from timestamp character.
·
Fld_Approved, Fld_Capital, Fld_Balance,
Fld_Hold, Fld_MinBalance, Fld_Period, Fld_InteRate, Fld_PenRate, Fld_FundRate,
Fld_Interst, Fld_Penalty, Fld_Fund, fld_Installment, fld_DueInte, fld_PBLine,
fle_PBPage, fld_DedAmount, Fld_OpenDeb, fld_OpenCre, are from the variable character of double.
·
All the other attributes are from varchar
character.
|
·
Fld_MemNo is a foreign key for tblmemeber
table.
·
Fld_GLAccountID is a foreign key for
tblglaccount.
·
Fld_AccountNumber is foreign key for
tbltransactions.
|
