0

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

enter image description here

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: enter image description here

0

1 Answer 1

2

I would organize the tables this way:

enter image description here

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
6
  • Thank you for your effort I appreciate it, but I have a couple of comments: in your design, you assigned the payments to shops but in my case, the shop can be rented today to customer number 1 and after year can be rented from customer 2 so how can I track the payment for the customers?, 2- cheque_in, why did you connect it to customers, not to payments details?? how can I get all the cheques for payments? (Payment details are the details for payment, payment value 2000$ it can be 1000$ cash and 500$ Visa, and 500$ cheques). Apr 29, 2021 at 17:09
  • 1)shop 1 can be rented to customer 1 then to 2 then to 1 again and you will check the dates. 2) Connect it to payment details if you prefer but I suggest you to divide branches by meaning. if check_in connected to payments makes sense to you just do it. 3) In Payment_details table use Payment_ID and then pay_id that can be a subcategory with other details about the total. Once again: all your questions are good questions. Don't think that me or other user here have the "golden rule". There's no such thing as "golden rule" and you have to invent your solution. Apr 29, 2021 at 19:15
  • Thank you for the advice I will try to improve the schema and get all your tips with my understanding of the system. I appreciate your help. Apr 30, 2021 at 0:23
  • Also search online "star schema" and "snowflake schema". It might give you a few ideas. Search for images of star schema on Google and copy the idea os someone else and adapt it to your problem Apr 30, 2021 at 4:53
  • I noticed that on Wikipedia if tou search for star schema there is a picture that shows the fact table that contains all the PK instead of having the PK on the dimensions. That is an idea too. Apr 30, 2021 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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