Database design in a system tool is used to provide the details of the project’s backend. In this project, the backend used to store the database is SQL Server 2005. SQL Server 2005 is a widely used as backend database. The project uses the database to keep of following table.
Table Name: LoginUser
Function:
The function of this table is to keep track of the administrator who has logged into the system by entering the appropriate user name and password.
Table Content:
Fields | Data type |
UniqueID | Char |
UserName | Char |
UserPassword | Char |
Table Name: Customize
Function:
The function of this table is to keep the details of basic company profile used to be displayed in home page.
Table Content:
Fields | Data type | |
CompanyName | Char | Primary Key |
CompanyContent | Text | |
CompanyLogo | Image |
Table Name: Designation
Function:
The function of this table is to keep the Types of designation of the company need.
Table Content:
Fields | Data type | Description |
DesignationType | Char | Primary Key |
Table Name: EmployerMast
Function:
The function of this table is to keep track of the Employer who employers in the company.
Table Content:
Fields | Data type | Description |
EmpID | Char | Primary Key |
EmpName | Char | |
Designation | Char | Foreign Key related Designation table |
DateOfBirth | Date | |
Sex | Char | |
Address | Char | |
Telephone1 | Number | |
Telephone2 | Number | |
SalaryAccount | Char | |
BloodGroup | Char | |
Basic | Number | |
HRA | Number | |
DA | Number | |
TA | Number | |
MA | Number | |
SA | Number | |
PF | Number | |
PT | Number | |
GI | Number | |
Canteen | Number | |
CT | Number | |
GS | Number | |
TD | Number | |
TotalSalary | Number | |
Remark1 | Char | |
Remark2 | Char |
Table Name: EmployerTran
Function:
The function of this table is to keep track of the Employer Salary compared to each month who employers in the company.
Table Content:
Fields | Data type | Description |
EmpID | Char | Foreign Key related EmployerMast table |
Salary Type | Char | C, R, Q |
Date | Date | Date of Transaction |
Table Name: Dealers
Function:
The function of this table is to keep details of all the Dealers Detail in the company.
Table Content:
Fields | Data type | Description |
DNo | Char | Primary Key |
DName | Char | |
DAdd1 | Char | |
DAdd2 | Char | |
DTel1 | Number | |
DTel2 | Number | |
DAcNo1 | Number | |
DAcNo2 | Number |
Table Name: Customer
Function:
The function of this table is to keep details of all the Customer Detail in the company.
Table Content:
Fields | Data type | Description |
CNo | Char | Primary Key |
CDName | Char | |
CAdd1 | Char | |
CAdd2 | Char | |
CTel1 | Number | |
CTel2 | Number | |
CAcNo1 | Number | |
CAcNo2 | Number |
Table Name: Group
Function:
The function of this table is to keep details of the type of group Names which are used in stock Item Detail.
Table Content:
Fields | Data type | Description |
GroupName | Char | Primary Key |
Table Name: StockItem
Function:
The function of this table is to keep details of the Stock Item in the Company.
Table Content:
Fields | Data type | Description |
ItemNo | Char | Primary Key |
ItemName | Char | |
GroupName | Char | Foreign Key related StockGroup table |
UnitName | Char | |
QtyInHand | Number | |
ItemPrice | Number | |
Warning | Number |
Table Name: PurchaseSalesMast
Function:
The function of this table is to keep details Purchase and Sales Detail but only the Bill No. Transaction Type, etc.
Table Content:
Fields | Data type | Description |
UniqueNo | Number | Primary Key |
BillNo | Char | |
TranType | Char | PU, SL |
Pay | Char | C, R, Q |
TranName | Char | |
TranDate | Date |
Table Name: PurchaseSalesTran
Function:
The function of this table is to keep details Purchase and Sales Detail Deals considering Item Qty, Rate, VAT, Total.
Table Content:
Fields | Data type | Description |
UniqueNo | Number | Foreign Key related PurchaseSalesMast table |
TranType | Char | PU, SL |
ItemNo | Foreign Key related Item table | |
Qty | Number | |
Rate | Number | |
Vat | Number | |
Total | Number |
Table Name: Bill
Function:
The function of this table is to keep details of all bills Transaction in the company.
Table Content:
Fields | Data type | Description |
Unique | Char | Primary Key |
BillNo | Char | |
BillDate | Date | |
BillType | Char | |
Insurance | Char | Default “AAAA” |
Pay | Char | C, R, Q |
BankAc | Char | Foreign Key related BankMast table |
Amount | Number |
Table Name: DTax
Function:
The function of this table is to keep details of all Duties and Taxes Transaction in the company.
Table Content:
Fields | Data type | Description |
Unique | Char | Primary Key |
DutiesType | Char | |
BillNo | Char | |
Date | Date | |
Pay | Char | C, R, Q |
BankAc | Char | Foreign Key related BankMast table |
Amount | Number |
Table Name: DisExp
Function:
The function of this table is to keep details of all Distribution Expenses Transaction in the company.
Table Content:
Fields | Data type | Description |
Unique | Char | Primary Key |
BillNo | Char | |
Date | Date | |
DistributionType | Char | |
Pay | Char | C, R, Q |
BankAc | Char | Foreign Key related BankMast table |
Note | Char | |
Amount | Number |
Table Name: OfficeMast
Function:
The function of this table is to keep details Purchase for Office use Detail but only the Bill No. Transaction Type, etc.
Table Content:
Fields | Data type | Description |
BillNo | Char | |
Pay | Char | C, R, Q |
DealerName | Char | R, C |
BillDealers | Char | |
Date | Date |
Table Name: OfficeTran
Function:
The function of this table is to keep details Purchase for Office use Detail Deals considering Item Qty, Rate, VAT, Total.
Table Content:
Fields | Data type | Description |
UniqueNo | Number | Foreign Key related OfficeMast table |
BillDealers | Char | |
Item | Char | |
Qty | Number | |
Rate | Number | |
Total | Number |
Table Name: Received
Function:
The function of this table is to keep details of all Received Transaction in the company.
Table Content:
Fields | Data type | Description |
UniqueNo | Number | Primary Key |
BillNo | Char | |
Date | Date | |
Pay | Char | C, R, Q |
ReceivedType | Char | |
ReceivedFrom | Char | |
Note | Char | |
Amount | Number |
Table Name: BankMast
Function:
The function of this table is to keep details all Bank Accounts and its Balances.
Table Content:
Fields | Data type | Description |
BankName | Char | |
BankAc | Number | Primary Key |
BankBal | Number |
Table Name: BankTran
Function:
The function of this table is to keep details all Bank Accounts and transactions.
Table Content:
Fields | Data type | Description |
BankAc | Number | Foreign Key related BankMast table |
BankWithdrawal | Number | |
BankDeposit | Number |
Table Name: LoanMast
Function:
The function of this table is to keep details all Loan Accounts and its Balances.
Table Content:
Fields | Data type | Description |
LoanName | Char | |
LoanAc | Number | Primary Key |
LoanBal | Number |
Table Name: LoanTran
Function:
The function of this table is to keep details all Loan Accounts and transactions.
Table Content:
Fields | Data type | Description |
LoanAc | Number | Foreign Key related LoanMast table |
LoanWithdrawal | Number | |
LoanDeposit | Number |
Table Name: Meeting
Function:
The function of this table is to keep details all Meeting Details in the company.
Table Content:
Fields | Data type | Description |
MUniqueID | char | Primary Key |
MDate | Date | |
MTime | Date | |
MNote | Char | |
MLocation | Char | |
MPriority | Char |
Table Name: Important
Function:
The function of this table is to keep details all Important Date Details in the company.
Table Content:
Fields | Data type | Description |
IUniqueID | char | Primary Key |
IDate | Date | |
INote | Char | |
IEvent | Char | |
IPriority | Char |
Table Name: StockSummaryRecord
Function:
The function of this table is to keep details all Stock Summary Details monthly conducted in the company.
Table Content:
Fields | Data type | Description |
ItemNo | Char | Primary Key |
SumaryDate | Date | |
Summary | Number |
Table Name: ProfitAndLoss
Function:
The function of this table is to keep details all Profit and Loss Details monthly conducted in the company.
Table Content:
Fields | Data type | Description |
ProfitLoassDate | Char | Primary Key |
Purchase | Number | |
Sales | Number | |
Duties and Taxes | Number | |
Bills | Number | |
Distribution Expenses | Number | |
Office Expenses | Number | |
Received Amount | Number |
