updated Accounting system data database design

Question

Many thanks for your valuable comments in my previous post. In my previous link Accounting system data database design

The primary key and foreign key was not assigned i was thinking handle from front hand side, but as your suggestion and comments i did changes in the database structure, kindly please check.

CREATE TABLE [dbo].[AccountType](
    [AccountTypeNo] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [AccountType] [nvarchar](50) NULL,
 CONSTRAINT [PK_AccountType] PRIMARY KEY CLUSTERED 
(
    [AccountTypeNo] 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

CREATE TABLE [dbo].[VoucherType](
    [VoucherType] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [VoucherName] [nvarchar](50) NULL,
 CONSTRAINT [PK_VoucherType] PRIMARY KEY CLUSTERED 
(
    [VoucherType] 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

CREATE TABLE [dbo].[Group](
    [GroupNo] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [GroupName] [nvarchar](150) NULL,
    [AccountTypeNo] [numeric](18, 0) NULL,
 CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED 
(
    [GroupNo] 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

ALTER TABLE [dbo].[Group]  WITH CHECK ADD  CONSTRAINT [FK_Group_AccountType] FOREIGN KEY([AccountTypeNo])
REFERENCES [dbo].[AccountType] ([AccountTypeNo])
GO

ALTER TABLE [dbo].[Group] CHECK CONSTRAINT [FK_Group_AccountType]
GO

CREATE TABLE [dbo].[Category](
    [CategoryNo] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [CategoryName] [nvarchar](150) NULL,
    [GroupNo] [numeric](18, 0) NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
    [CategoryNo] 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

ALTER TABLE [dbo].[Category]  WITH CHECK ADD  CONSTRAINT [FK_Category_Group] FOREIGN KEY([GroupNo])
REFERENCES [dbo].[Group] ([GroupNo])
GO

ALTER TABLE [dbo].[Category] CHECK CONSTRAINT [FK_Category_Group]
GO

CREATE TABLE [dbo].[SubCategory](
    [SubCategoryNo] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [SubCategory] [nvarchar](500) NULL,
    [CategoryNo] [numeric](18, 0) NULL,
    [SalesSign] [nvarchar](50) NULL,
    [ReceiptSign] [nvarchar](50) NULL,
    [PurchaseSign] [nvarchar](50) NULL,
    [PaymentSign] [nvarchar](50) NULL,
 CONSTRAINT [PK_SubCategory] PRIMARY KEY CLUSTERED 
(
    [SubCategoryNo] 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

ALTER TABLE [dbo].[SubCategory]  WITH CHECK ADD  CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryNo])
REFERENCES [dbo].[Category] ([CategoryNo])
GO

ALTER TABLE [dbo].[SubCategory] CHECK CONSTRAINT [FK_SubCategory_Category]
GO


CREATE TABLE [dbo].[Company](
    [CompanyNo] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](150) NULL,
    [Address] [nvarchar](150) NULL,
    [Telephone] [nvarchar](50) NULL,
    [Fax] [nvarchar](50) NULL,
    [EmailAddress] [nvarchar](50) NULL,
    [Currency] [numeric](18, 0) NULL,
 CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED 
(
    [CompanyNo] 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

ALTER TABLE [dbo].[Company]  WITH CHECK ADD  CONSTRAINT [FK_Company_Currency] FOREIGN KEY([Currency])
REFERENCES [dbo].[Currency] ([CurrencyNo])
GO

ALTER TABLE [dbo].[Company] CHECK CONSTRAINT [FK_Company_Currency]
GO

CREATE TABLE [dbo].[Costcenter](
    [CostcenterNo] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [CostCenter] [nvarchar](50) NULL,
 CONSTRAINT [PK_Costcenter] PRIMARY KEY CLUSTERED 
(
    [CostcenterNo] 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

CREATE TABLE [dbo].[Currency](
    [CurrencyNo] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [CurrencyCode] [nvarchar](50) NULL,
    [CurrencyDesc] [nvarchar](50) NULL,
    [CurrencyDecimalPlace] [numeric](18, 0) NULL,
    [CurrencyIntegral] [nvarchar](50) NULL,
 CONSTRAINT [PK_Currency] PRIMARY KEY CLUSTERED 
(
    [CurrencyNo] 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

CREATE TABLE [dbo].[Ledger](
    [LedgerNo] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [LedgerName] [nvarchar](500) NULL,
    [SubCategoryNo] [numeric](18, 0) NULL,
    [VATNo] [nvarchar](50) NULL,
    [CreditDays] [numeric](18, 0) NULL,
    [OpeningBalance] [numeric](18, 2) NULL,
    [BankNameifany] [nvarchar](50) NULL,
    [BankACifany] [nvarchar](50) NULL,
    [CurrencyNo] [numeric](18, 0) 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

ALTER TABLE [dbo].[Ledger]  WITH CHECK ADD  CONSTRAINT [FK_Ledger_SubCategory] FOREIGN KEY([SubCategoryNo])
REFERENCES [dbo].[SubCategory] ([SubCategoryNo])
GO

ALTER TABLE [dbo].[Ledger] CHECK CONSTRAINT [FK_Ledger_SubCategory]
GO


CREATE TABLE [dbo].[Journal](
    [JournalNo] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [JournalNumber] [nvarchar](50) NULL,
    [Date] [date] NULL,
    [Vouchertype] [numeric](18, 0) NULL,
    [BillDate] [date] NULL,
    [CostCenter] [numeric](18, 0) NULL,
    [Narration] [nvarchar](500) NULL,
    [CompanyNo] [numeric](18, 0) NULL,
 CONSTRAINT [PK_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

ALTER TABLE [dbo].[Journal]  WITH CHECK ADD  CONSTRAINT [FK_Journal_Company] FOREIGN KEY([CompanyNo])
REFERENCES [dbo].[Company] ([CompanyNo])
GO

ALTER TABLE [dbo].[Journal] CHECK CONSTRAINT [FK_Journal_Company]
GO

ALTER TABLE [dbo].[Journal]  WITH CHECK ADD  CONSTRAINT [FK_Journal_Costcenter] FOREIGN KEY([CostCenter])
REFERENCES [dbo].[Costcenter] ([CostcenterNo])
GO

ALTER TABLE [dbo].[Journal] CHECK CONSTRAINT [FK_Journal_Costcenter]
GO

ALTER TABLE [dbo].[Journal]  WITH CHECK ADD  CONSTRAINT [FK_Journal_VoucherType] FOREIGN KEY([Vouchertype])
REFERENCES [dbo].[VoucherType] ([VoucherType])
GO

ALTER TABLE [dbo].[Journal] CHECK CONSTRAINT [FK_Journal_VoucherType]
GO

CREATE TABLE [dbo].[JournalDetails](
    [JournalDetailsNo] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [JournalNo] [numeric](18, 0) NULL,
    [LedgerNo] [numeric](18, 0) NULL,
    [DebitAmount] [numeric](18, 2) NULL,
    [CreditAmount] [numeric](18, 2) NULL,
 CONSTRAINT [PK_JournalDetails] PRIMARY KEY CLUSTERED 
(
    [JournalDetailsNo] 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

ALTER TABLE [dbo].[JournalDetails]  WITH CHECK ADD  CONSTRAINT [FK_JournalDetails_Journal] FOREIGN KEY([JournalNo])
REFERENCES [dbo].[Journal] ([JournalNo])
GO

ALTER TABLE [dbo].[JournalDetails] CHECK CONSTRAINT [FK_JournalDetails_Journal]
GO

Answer 1

Why did you choose numeric(18,0) for your identity datatype? Are you expecting anywhere near that many rows? I would think int (allows 2,147,483,647 positive values) is enough in almost any system. If you need more than 2 billion rows you could use bigint.

I would suggest avoiding datatype names as column names. Date is a datatype. As a column name it is sort of useless. Not only do you have constantly have to wrap the name as [date] it means nothing. What date is it? The date created, date last edited, some other date relevant to the data?

Also a thing I find annoying is when a column name changes from table to table. One example is in your Company table. You have a column named Currency but that refers to CurrencyNo in the Currency table. It is still a CurrencyNo.

Generally speaking I think you would benefit from a bit analysis of datatypes. You have varchar(50) all over the place. Some places this is way too long like for phone numbers. And is too short for things like email addresses.

You seem to have good data integrity. Looks like proper usage of primary keys and foreign keys which will make sure your data is valid. Probably need to dig into constraints for some columns here and there but overall this is pretty reasonable.