Database Design – One-Time and Monthly Payments in SQL Server

database-designschemasql servert-sql

I'm working on leasing shops system and here is my database for now:

I have contracts these contracts have 2 types, lease for a duration (year) and the payment will be once, and the other type is lease for a duration but the payments will be monthly.

I already did the yearly payments, but I don't know what is the best approach to do the monthly payments, I'm thinking this way, to create a new table and add the all months for the contract duration to that table E.G. if I have a 12-month contract I will add 12 records on the table each record for one month, but in this case, It will be hard to manage all scenarios if the user needs to update the contract or change the duration for it.

Is this the right way to do it, ist it good that I separate the payments for the once payment contract (I called it yearly payments) in different tables from the monthly payments ?? if NOT, what the best approach in my case to use?? I don't want to spend a lot of hours working on the wrong schema design.
Thank you.
here is the database script:

CREATE TABLE [dbo].[cheque_in](
[ID] [int] IDENTITY(1,1) NOT NULL,
[account_number] [int] NULL,
[cheque_number] [int] NOT NULL,
[cheque_value] [decimal](10, 2) NOT NULL,
[cheque_due_date] [date] NOT NULL,
[cheque_bank] [nvarchar](55) NULL,
[cheque_branch] [nvarchar](55) NULL,
[holder_name] [nvarchar](250) NULL,
[date] [date] NOT NULL,
[note] [nvarchar](max) NULL,
[pay_id] [int] NULL,
CONSTRAINT [PK_cheque_in] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[contracts]    Script Date: 29/04/2021 03:56:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[contracts](
[ID] [int] IDENTITY(1,1) NOT NULL,
[cust_id] [int] NOT NULL,
[duration] [int] NOT NULL,
[price] [decimal](10, 2) NOT NULL,
[tax] [decimal](10, 2) NULL,
[usage] [nvarchar](20) NOT NULL,
[rent_type] [nvarchar](10) NOT NULL,
[price2] [decimal](10, 2) NULL,
[note2] [nvarchar](max) NULL,
[date_start] [date] NOT NULL,
[date_end] [date] NOT NULL,
[note] [nvarchar](max) NULL,
[image] [varbinary](max) NULL,
[app_user] [nvarchar](20) NULL,
[archive] [bit] NOT NULL,


CONSTRAINT [PK_contracts] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[contracts_shops]    Script Date: 29/04/2021 03:56:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[contracts_shops](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [contract_id] [int] NOT NULL,
    [shop_id] [int] NOT NULL,
    [date] [datetime] NOT NULL,
 CONSTRAINT [PK_contracts_shops] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[customers]    Script Date: 29/04/2021 03:56:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[customers](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [cust_id] [int] NOT NULL,
    [name] [nvarchar](50) NULL,
    [nickname] [nvarchar](50) NULL,
    [city] [nvarchar](50) NULL,
    [phone1] [nvarchar](10) NULL,
    [phone2] [nvarchar](10) NULL,
    [phone3] [nvarchar](10) NULL,
    [email] [nvarchar](50) NULL,
    [image] [varbinary](max) NULL,
    [date] [date] NULL,
    [app_user] [nvarchar](20) NULL,
    [archive] [bit] NULL,
 CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED 
(
    [cust_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[invoices]    Script Date: 29/04/2021 03:56:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[invoices](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [conid] [int] NOT NULL,
    [value] [decimal](10, 2) NOT NULL,
    [due_date] [date] NULL,
    [date] [date] NULL,
 CONSTRAINT [PK_due_payments] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[payments]    Script Date: 29/04/2021 03:56:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[payments](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [invid] [int] NULL,
    [note] [nvarchar](max) NULL,
    [date] [date] NULL,
    [app_user] [nvarchar](20) NULL,
 CONSTRAINT [PK_invoices] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[payments_details]    Script Date: 29/04/2021 03:56:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[payments_details](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [pay_id] [int] NOT NULL,
    [value] [decimal](10, 2) NULL,
    [method] [nvarchar](20) NULL,
    [date] [date] NULL,
 CONSTRAINT [PK_Payment_yearly_details] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[shops]    Script Date: 29/04/2021 03:56:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[shops](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [shop_id] [int] NOT NULL,
    [size] [decimal](4, 1) NULL,
    [location] [nvarchar](50) NULL,
    [floor] [nvarchar](10) NULL,
    [status] [bit] NULL,
    [date] [date] NULL,
    [app_user] [nvarchar](20) NULL,
    [archive] [bit] NULL,
 CONSTRAINT [PK_shops] PRIMARY KEY CLUSTERED 
(
    [shop_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[users]    Script Date: 29/04/2021 03:56:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

I made some changes to the database now the Diagram look like this:

Best Answer

I would organize the tables this way:

You said:

I don't want to spend a lot of hours working on the wrong schema design.

I suggest you to spend a lot of time on your schema design. Put at the center what you want to focus on, in our case the Shops are at the core of this leasing shops system.

Then create the dimensions which are the branches that comes form the core of your database and try to divide them by meaningful topics.

In this case I have divided it in 3 main branches:

Contracts
Payments and Invoices
Customers and Check-in

Keep in mind that I have done this without having any grip of what do you have in those tables so this can be perfectly wrong.

I just wanted to teach you a method. Now use this method to create your solution.

And here comes the code:

USE [test]
GO

/****** Object:  Table [dbo].[cheque_in]    Script Date: 29/04/2021 17:39:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[cheque_in](
    [Check_in_ID] [int] NULL,
    [Customer_id] [int] IDENTITY(1,1) NOT NULL,
    [account_number] [int] NULL,
    [cheque_number] [int] NOT NULL,
    [cheque_value] [decimal](10, 2) NOT NULL,
    [cheque_due_date] [date] NOT NULL,
    [cheque_bank] [nvarchar](55) NULL,
    [cheque_branch] [nvarchar](55) NULL,
    [holder_name] [nvarchar](250) NULL,
    [date] [date] NOT NULL,
    [note] [nvarchar](max) NULL,
    [pay_id] [int] NULL,
 CONSTRAINT [PK_cheque_in] PRIMARY KEY CLUSTERED 
(
    [Customer_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[cheque_in]  WITH CHECK ADD  CONSTRAINT [FK_cheque_in_customers] FOREIGN KEY([Customer_id])
REFERENCES [dbo].[customers] ([Customer_id])
GO

ALTER TABLE [dbo].[cheque_in] CHECK CONSTRAINT [FK_cheque_in_customers]
GO


USE [test]
GO

/****** Object:  Table [dbo].[contracts]    Script Date: 29/04/2021 17:39:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[contracts](
    [Contract_ID] [int] IDENTITY(1,1) NOT NULL,
    [Customer_id] [int] NOT NULL,
    [duration] [int] NOT NULL,
    [price] [decimal](10, 2) NOT NULL,
    [tax] [decimal](10, 2) NULL,
    [usage] [nvarchar](20) NOT NULL,
    [rent_type] [nvarchar](10) NOT NULL,
    [price2] [decimal](10, 2) NULL,
    [note2] [nvarchar](max) NULL,
    [date_start] [date] NOT NULL,
    [date_end] [date] NOT NULL,
    [note] [nvarchar](max) NULL,
    [image] [varbinary](max) NULL,
    [app_user] [nvarchar](20) NULL,
    [archive] [bit] NOT NULL,
 CONSTRAINT [PK_contracts] PRIMARY KEY CLUSTERED 
(
    [Contract_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[contracts]  WITH CHECK ADD  CONSTRAINT [FK_contracts_contracts_shops] FOREIGN KEY([Contract_ID])
REFERENCES [dbo].[contracts_shops] ([Contract_ID])
GO

ALTER TABLE [dbo].[contracts] CHECK CONSTRAINT [FK_contracts_contracts_shops]
GO


USE [test]
GO

/****** Object:  Table [dbo].[contracts_shops]    Script Date: 29/04/2021 17:40:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[contracts_shops](
    [Contract_ID] [int] NOT NULL,
    [Shop_ID] [int] NOT NULL,
    [date] [datetime] NOT NULL,
 CONSTRAINT [PK_contracts_shops] PRIMARY KEY CLUSTERED 
(
    [Contract_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[contracts_shops]  WITH CHECK ADD  CONSTRAINT [FK_contracts_shops_shops] FOREIGN KEY([Shop_ID])
REFERENCES [dbo].[shops] ([Shop_ID])
GO

ALTER TABLE [dbo].[contracts_shops] CHECK CONSTRAINT [FK_contracts_shops_shops]
GO


USE [test]
GO

/****** Object:  Table [dbo].[customers]    Script Date: 29/04/2021 17:40:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[customers](
    [Shop_ID] [int] IDENTITY(1,1) NOT NULL,
    [Customer_id] [int] NOT NULL,
    [name] [nvarchar](50) NULL,
    [nickname] [nvarchar](50) NULL,
    [city] [nvarchar](50) NULL,
    [phone1] [nvarchar](10) NULL,
    [phone2] [nvarchar](10) NULL,
    [phone3] [nvarchar](10) NULL,
    [email] [nvarchar](50) NULL,
    [image] [varbinary](max) NULL,
    [date] [date] NULL,
    [app_user] [nvarchar](20) NULL,
    [archive] [bit] NULL,
 CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED 
(
    [Customer_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[customers]  WITH CHECK ADD  CONSTRAINT [FK_customers_shops] FOREIGN KEY([Shop_ID])
REFERENCES [dbo].[shops] ([Shop_ID])
GO

ALTER TABLE [dbo].[customers] CHECK CONSTRAINT [FK_customers_shops]
GO


USE [test]
GO

/****** Object:  Table [dbo].[invoices]    Script Date: 29/04/2021 17:40:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[invoices](
    [Payment_ID] [int] IDENTITY(1,1) NOT NULL,
    [conid] [int] NOT NULL,
    [value] [decimal](10, 2) NOT NULL,
    [due_date] [date] NULL,
    [date] [date] NULL,
 CONSTRAINT [PK_invoices] PRIMARY KEY CLUSTERED 
(
    [Payment_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


USE [test]
GO

/****** Object:  Table [dbo].[payments]    Script Date: 29/04/2021 17:40:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[payments](
    [Payment_ID] [int] NOT NULL,
    [Shop_ID] [int] IDENTITY(1,1) NOT NULL,
    [invid] [int] NULL,
    [note] [nvarchar](max) NULL,
    [date] [date] NULL,
    [app_user] [nvarchar](20) NULL,
 CONSTRAINT [PK_payments] PRIMARY KEY CLUSTERED 
(
    [Payment_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[payments]  WITH CHECK ADD  CONSTRAINT [FK_payments_invoices] FOREIGN KEY([Payment_ID])
REFERENCES [dbo].[invoices] ([Payment_ID])
GO

ALTER TABLE [dbo].[payments] CHECK CONSTRAINT [FK_payments_invoices]
GO

ALTER TABLE [dbo].[payments]  WITH CHECK ADD  CONSTRAINT [FK_payments_shops] FOREIGN KEY([Shop_ID])
REFERENCES [dbo].[shops] ([Shop_ID])
GO

ALTER TABLE [dbo].[payments] CHECK CONSTRAINT [FK_payments_shops]
GO


USE [test]
GO

/****** Object:  Table [dbo].[payments_details]    Script Date: 29/04/2021 17:41:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[payments_details](
    [Payment_ID] [int] IDENTITY(1,1) NOT NULL,
    [pay_id] [int] NOT NULL,
    [value] [decimal](10, 2) NULL,
    [method] [nvarchar](20) NULL,
    [date] [date] NULL,
 CONSTRAINT [PK_payments_details] PRIMARY KEY CLUSTERED 
(
    [Payment_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[payments_details]  WITH CHECK ADD  CONSTRAINT [FK_payments_details_payments] FOREIGN KEY([Payment_ID])
REFERENCES [dbo].[payments] ([Payment_ID])
GO

ALTER TABLE [dbo].[payments_details] CHECK CONSTRAINT [FK_payments_details_payments]
GO


USE [test]
GO

/****** Object:  Table [dbo].[shops]    Script Date: 29/04/2021 17:41:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[shops](
    [Shop_ID] [int] NOT NULL,
    [size] [decimal](4, 1) NULL,
    [location] [nvarchar](50) NULL,
    [floor] [nvarchar](10) NULL,
    [status] [bit] NULL,
    [date] [date] NULL,
    [app_user] [nvarchar](20) NULL,
    [archive] [bit] NULL,
 CONSTRAINT [PK_shops] PRIMARY KEY CLUSTERED 
(
    [Shop_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Best Answer

Related Solutions

SQL Server Hierarchical Order – Parent-Child Tree Hierarchical ORDER BY

Separate table for ‘scheduled payments’ and ‘completed payments’

Related Question