Hi,
I have created table to create accounting system, The Sales, Receipt, Purchase, Payment and Journal eateries all saved in One Master and Transaction table and according to chart of account the trial balance, P&L and Balance sheet will show. Please let me know the tables what i'm using is correct or any other idea need to be implemented.
Master.
CREATE TABLE [dbo].[Mst_COA](
[COANo] [int] IDENTITY(1,1) NOT NULL,
[COAName] [nvarchar](500) NULL,
[COA_COANo] [int] NULL,
[COAAccountNo] [int] NULL,
[COAActive] [int] NULL,
[COATypeNo] [int] NOT NULL,
[COACashFlowNo] [int] NULL,
[COAOpenBalance] [numeric](18, 4) NULL,
[COACompNo] [int] NULL,
[COASales] [nvarchar](50) NULL,
[COAReceipt] [nvarchar](50) NULL,
[COAPurchase] [nvarchar](50) NULL,
[COAPayment] [nvarchar](50) NULL,
CONSTRAINT [PK_Mst_COASubType] PRIMARY KEY CLUSTERED
(
[COANo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Ledger table including everything, supplier, customer and other ledger such as banks etc.
CREATE TABLE [dbo].[Mst_Ledger](
[LedgerNo] [int] IDENTITY(1,1) NOT NULL,
[LedgerName] [nvarchar](500) NULL,
[LedgerTypeNo] [int] NULL,
[LedgerCOANo] [int] NULL,
[LedgerTelephone] [nvarchar](50) NULL,
[LedgerFax] [nvarchar](50) NULL,
[LedgerEmail] [nvarchar](500) NULL,
[LedgerWebsite] [nvarchar](500) NULL,
[LedgerContactPer] [nvarchar](500) NULL,
[LegderContactPerGSM] [nvarchar](50) NULL,
[LedgerActive] [int] NULL,
[LedgeropenBalance] [numeric](18, 4) NULL,
[LedgerCrdPeridDays] [int] NULL,
[LedgeropenBalanceDate] [date] NULL,
[LedgerTax] [int] NULL,
[LedgerPaymentTermNo] [int] NULL,
[LedgerBankName] [nvarchar](500) NULL,
[LedgerBankAccNo] [nvarchar](150) NULL,
[LedgerSwiftCode] [nvarchar](150) NULL,
[LedgerIBANCode] [nvarchar](150) NULL,
[LedgerAccnCurrNo] [int] NULL,
[LedgerCompNo] [int] NULL,
[LedgerVATNo] [nvarchar](150) NULL,
CONSTRAINT [PK_Ledger] PRIMARY KEY CLUSTERED
(
[LedgerNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
below is two transaction table include all accounting transaction.
CREATE TABLE [dbo].[Trn_Journal](
[JournalNo] [int] IDENTITY(1,1) NOT NULL,
[JournalNumber] [int] NULL,
[JournalDate] [date] NULL,
[JournalVoucherNo] [int] NULL,
[JournalCurrencyNo] [int] NULL,
[JournalCurExRate] [numeric](18, 4) NULL,
[JournalRemark] [nvarchar](500) NULL,
[JournalCompNo] [int] NULL,
[JournalTranRefNo2] [int] NULL,
[JournalShowin] [int] NULL,
[JournalReferenceNo] [nvarchar](150) NULL,
[JournalBillDate] [date] NULL,
[JournalPost] [int] NULL,
CONSTRAINT [PK_Trn_Journal] PRIMARY KEY CLUSTERED
(
[JournalNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Detail Table.
CREATE TABLE [dbo].[Trn_JournalDetails](
[JournaFKlNo] [int] NULL,
[JournalNo_N] [int] IDENTITY(1,1) NOT NULL,
[JournalTranRefNo] [int] NULL,
[JournalLedgerNo] [int] NULL,
[JournalDebit] [numeric](18, 4) NULL,
[JournalCredit] [numeric](18, 4) NULL,
[JournalJobNo] [int] NULL,
[JournalCostCenterNo] [int] NULL,
[JournalAssetNo] [int] NULL,
[JournalItemNo] [int] NULL,
[JournalItemQty] [numeric](18, 3) NULL,
CONSTRAINT [PK_Trn_JournalDetails] PRIMARY KEY CLUSTERED
(
[JournalNo_N] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
regards,
Basit.