3
\$\begingroup\$

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?

\$\endgroup\$
4
  • \$\begingroup\$ Please do not update the code in your question to incorporate feedback from answers, doing so goes against the Question + Answer style of Code Review. This is not a forum where you should keep the most updated version in your question. Please see what you may and may not do after receiving answers. \$\endgroup\$
    – Mast
    Aug 21, 2022 at 9:43
  • \$\begingroup\$ Thank you, i'm new in codereview, where should i post updated query? \$\endgroup\$ Aug 21, 2022 at 10:05
  • \$\begingroup\$ In a new question. Feel free to add links back and forth. \$\endgroup\$
    – Mast
    Aug 21, 2022 at 11:02
  • \$\begingroup\$ Many thanks, i posted new. codereview.stackexchange.com/questions/279019/… \$\endgroup\$ Aug 21, 2022 at 11:26

2 Answers 2

8
\$\begingroup\$

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"?
\$\endgroup\$
1
  • 4
    \$\begingroup\$ All great points. I would also add there are no primary keys, foreign keys or even an attempt at any referential integrity. \$\endgroup\$
    – Sean Lange
    Aug 18, 2022 at 14:39
4
\$\begingroup\$

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

\$\endgroup\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.