一次付款和每月付款的數據庫設計

April 29, 2021

我正在研究租賃商店系統，這是我現在的數據庫：

我有契約，這些契約有兩種類型，租期（年），付款一次，另一種類型是租期，但按月付款。

我已經完成了年度付款，但我不知道每月付款的最佳方法是什麼，我這樣想，創建一個新表並將契約期限的所有月份添加到該表 EG 如果我有一個 12 個月的契約，我會在表上添加 12 條記錄，每條記錄一個月，但是在這種情況下，如果使用者需要更新契約或更改契約的期限，將很難管理所有場景。

這是正確的做法嗎，我將一次性付款契約的付款（我稱之為年度付款）與每月付款分開在不同的表格中是否很好？如果不是，在我的情況下使用什麼最好的方法？我不想在錯誤的架構設計上花費大量時間。謝謝你。這是數據庫腳本：

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

我對數據庫進行了一些更改，現在圖表如下所示：

我會以這種方式組織表格：

你說：

我不想在錯誤的架構設計上花費大量時間。

我建議您在架構設計上花費大量時間。把你想要關注的放在中心，在我們的例子中，商店是這個租賃商店系統的核心。

然後創建維度，這些維度是構成數據庫核心的分支，並嘗試將它們劃分為有意義的主題。

在這種情況下，我將其分為 3 個主要分支：

契約
付款和發票
客戶和簽到

請記住，我在沒有掌握這些表格中的內容的情況下完成了此操作，因此這可能是完全錯誤的。

我只是想教你一個方法。現在使用這種方法來創建您的解決方案。

程式碼如下：

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

引用自：https://dba.stackexchange.com/questions/290644

一次付款和每月付款的數據庫設計

相關問答

有沒有一種簡單的方法可以讓一個基於另一列的非 IDENTITY 自動遞增列？

如果文件位於 RAID 上，我應該對數據庫文件進行分區以提高性能嗎？

建議創建表或設計儲存過程以刪除 while 循環

SQL案例問題

保留每個表中每個值的完整歷史記錄

分區切換與模式傳輸