Accounting system data database design

Question

I have created an accounting database as per my accounting knowledge.

I want to check: Is that database correct which I design for the accounting system?

I have table called Mst_AccountGroup(Asset,Liabilities,Capital/Equity,Income & Expense data to create a chart of the account).

The database structure is given below.

CREATE TABLE [dbo].[Mst_AccountGroup](
    [AccountGrp_No] [int] NULL,
    [AccountGrp_Name] [nvarchar](50) NULL,
    [AccountGrp_Type] [nvarchar](50) NULL,
    [AccountGrp_Increase] [nvarchar](50) NULL,
    [AccountGrp_Decrease] [nvarchar](50) NULL,
    [AccountGrp_Sign] [nvarchar](50) NULL,
    [AccountGrp_SalesSign] [nvarchar](50) NULL,
    [AccountGrp_ReceiptSign] [nvarchar](50) NULL,
    [AccountGrp_PurchaseSign] [nvarchar](50) NULL,
    [AccountGrp_PaymentSign] [nvarchar](50) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Mst_AccountCategory](
    [AccountCat_No] [int] NULL,
    [AccountCat_Name] [nvarchar](500) NULL,
    [AccountCat_AccountGrpNo] [int] NULL,
    [AccountCat_Sign] [nvarchar](50) NULL,
    [AccountCat_SalesSign] [nvarchar](50) NULL,
    [AccountCat_ReceiptSign] [nvarchar](50) NULL,
    [AccountCat_PurchaseSign] [nvarchar](50) NULL,
    [AccountCat_PaymentSign] [nvarchar](50) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Mst_AccountSubCategory](
    [AccountSubCat_No] [int] NULL,
    [AccountSubCat_AccountCatNo] [int] NULL,
    [AccountSubCat_Name] [nvarchar](500) NULL,
    [AccountSubCat_SalesSign] [nvarchar](50) NULL,
    [AccountSubCat_ReceiptSign] [nvarchar](50) NULL,
    [AccountSubCat_PurchaseSign] [nvarchar](50) NULL,
    [AccountSubCat_PaymentSign] [nvarchar](50) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Trn_Ledger](
    [Ledger_No] [int] NULL,
    [Ledger_Name] [nvarchar](500) NULL,
    [Ledger_AccountSubCatNo] [int] NULL,
    [Ledger_VATNumber] [nvarchar](250) NULL,
    [Ledger_CreditDays] [numeric](18, 0) NULL,
    [Ledger_OpeningBalance] [numeric](18, 3) NULL,
    [Ledger_BankName] [nvarchar](500) NULL,
    [Ledger_BankAC] [nvarchar](500) NULL,
    [Ledger_SwiftCode] [nvarchar](500) NULL,
    [Ledger_BankAddress] [nvarchar](500) NULL,
    [Ledger_Countory] [nvarchar](500) NULL,
    [Ledger_VATPercent] [numeric](18, 3) NULL
) ON [PRIMARY]

GO

I have only one transaction table which include Sales, Receipt and Purchase, Payment and Journal Entries. Vouchers are separating through the [AccTran_VchType] field. Note that all the transaction are double entry.

Sales, Receipt and Purchase entries, on those voucher the user cannot make a mistake to put the ledger on the credit or debit side, because from the chart of the account once set then when selecting a particular ledger then either it will be credit or debit, and the system will tell the user.

CREATE TABLE [dbo].[Trn_AccountTransactionList](
    [AccTran_No] [int] NULL,
    [AccTran_Number] [nvarchar](50) NULL,
    [AccTran_Date] [date] NULL,
    [AccTran_Remark] [nvarchar](500) NULL,
    [AccTran_VchType] [nvarchar](50) NULL,
    [AccTran_RefNo] [nvarchar](250) NULL,
    [AccTran_BillDate] [date] NULL,
    [AccTran_Post] [numeric](18, 0) NULL,
    [AccTran_Number2] [numeric](18, 0) NULL,
    [AccTran_InvoiceNo] [numeric](18, 0) NULL,
    [AccTran_PoNo] [numeric](18, 0) NULL,
    [AccTran_PDC] [numeric](18, 0) NULL,
    [Acc_TranLedgerNo] [numeric](18, 0) NULL,
    [Acc_TranAmount] [numeric](18, 3) NULL,
    [Acc_CompNo] [numeric](18, 0) NULL,
    [Acc_Updatedby] [nvarchar](50) NULL,
    [Acc_UpdatedDatetime] [datetime] NULL,
    [Acc_BankDate] [date] NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[Trn_AccountTransaction](
    [AccTran_No] [int] NULL,
    [AccTran_No_N] [int] NULL,
    [AccTran_AccountLedgerNo] [int] NULL,
    [AccTran_AmountDR] [numeric](18, 3) NULL,
    [AccTran_AmountCR] [numeric](18, 3) NULL,
) ON [PRIMARY]

GO

Trial balance query.

CREATE VIEW [dbo].[View_TrialBalance]
AS
SELECT     TOP (100) PERCENT AccountCat_Name, AccountGrp_Name, DR, CR, AccountGrp_Type, AccountGrp_No, Balance, AccountSubCat_No, CASE WHEN AccountGrp_No IN (1,
                       5) THEN Balance ELSE 0 END AS TrialDebit, CASE WHEN AccountGrp_No IN (2, 3, 4) THEN Balance ELSE 0 END AS TrialCredit, AccountSubCat_Name, Ledger_No,
                      Ledger_Name, AccTran_Date, Comp_No, Comp_Name, Acc_CompNo, Comp_Add, Comp_Tel, Comp_Fax, Comp_Country
FROM         dbo.View_Ledger
ORDER BY AccountSubCat_No

GO View_P&LandBalanceSheet

CREATE VIEW [dbo].[View_P&LandBalanceSheet]
AS
SELECT     TOP (100) PERCENT AccountCat_Name, AccountGrp_Name, DR, CR, AccountGrp_Type, Balance, TrialDebit, TrialCredit, AccountSubCat_No,
                      CASE WHEN AccountGrp_Type = 'P&L' THEN TrialDebit ELSE 0 END AS InDebit, CASE WHEN AccountGrp_Type = 'P&L' THEN TrialCredit ELSE 0 END AS InCredit,
                      CASE WHEN AccountGrp_Type = 'BS' THEN TrialDebit ELSE 0 END AS BSDebit, CASE WHEN AccountGrp_Type = 'BS' THEN TrialCredit ELSE 0 END AS BSCredit,
                      AccountSubCat_Name, Ledger_No, Ledger_Name, AccTran_Date, Comp_No, Comp_Name, Acc_CompNo, Comp_Add, Comp_Tel, Comp_Fax, Comp_Country,
                      YEAR(AccTran_Date) AS YRS
FROM         dbo.View_TrialBalance
WHERE     (NOT (AccountCat_Name IS NULL))
ORDER BY AccountSubCat_No

GO

Note that all other reports are coming correctly based on above Transaction table. Is the database structure perfect?

Answer 1

Some quick remarks:

• Why the (incomprehensible) prefixes in the table names? Why not use the SCHEMA instead (and use proper words)?

• Why are all your table fields prefixed with the table name (or rather, some awful abbreviation of it)?

  • I kinda do get it, this is so you can write queries which involve multiple tables and don't have to worry about conflicting field names, but IMHO I have always felt that is a bad reason to add so much duplicate information. I've always found it more elegant to use AS to rename any conflicting field names in queries.

• Why "No" instead of "ID"?

• Check your spelling, e.g. "Countory".

• Do not needlessly abbreviate, e.g. "VchType", "PoNo".

  • What is "No_N"?

Answer 2

Top 100 percent in a view is useless. Get rid of that. It does NOT ensure the order that rows will be returned from the view and logically it wouldn't work anyway as you could have conflicting order by clauses.

Even worse is that you have nested views which is a guaranteed way to make sure the performance tanks.

https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/the-seven-sins-against-tsql-performance/#seven