Database design for one payment and monthly payments

All we need is an easy explanation of the problem, so here it is.

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

Database design for one payment and monthly payments

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,
[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,
[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,
[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:
Database design for one payment and monthly payments

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I would organize the tables this way:

Database design for one payment and monthly payments

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,
    
[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,
[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,
[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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply