支付宝开发表设计 适用于 所有支付业务

支付信息与业务关联表，主要处理 合并支付 等 一对多关系的支付：

支付基础信息：

脚本如下：

CREATE TABLE [dbo].[PayRealation](
	[PayOrderNo] [varchar](50) NOT NULL,
	[GoodsOrderNo] [nvarchar](50) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支付订单号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayRealation', @level2type=N'COLUMN',@level2name=N'PayOrderNo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品订单号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayRealation', @level2type=N'COLUMN',@level2name=N'GoodsOrderNo'
GO

EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'支付信息与业务订单关系' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayRealation'
GO

CREATE TABLE [dbo].[PayBasic](
	[PayOrderNo] [varchar](50) NOT NULL,
	[TotalAmount] [decimal](18, 4) NOT NULL,
	[PayType] [varchar](3) NOT NULL,
	[PayStatus] [varchar](3) NOT NULL,
	[Currency] [varchar](10) NOT NULL,
	[ThirdOrderNo] [varchar](50) NULL,
	[CreateTime] [datetime] NOT NULL,
	[ThirdNo] [tinyint] NOT NULL,
	[UpdateTime] [datetime] NULL,
	[BankId] [varchar](50) NULL,
	[BankName] [nvarchar](80) NULL,
	[CardNumber] [varchar](30) NULL,
	[CreateUserId] [int] NULL,
	[CreateUserName] [varchar](50) NULL,
	[ISQRequestTime] [datetime] NULL,
	[LatestTime] [datetime] NULL,
	[ActualTime] [datetime] NULL,
	[VerifierId] [int] NULL,
	[VerifierName] [nvarchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支付订单号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'PayOrderNo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支付总金额' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'TotalAmount'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支付类别(10：在线支付；20：线下转账)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'PayType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支付状态(10-待支付；20-支付失败；30-支付成功；40：已关闭；50：取消；)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'PayStatus'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'币制（默认RMB）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'Currency'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'第三方支付订单号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'ThirdOrderNo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'CreateTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'第三方交易编号( 1:支付宝交易号；2:汇付天下; 3-智惠支付;4：首信易；5：微信)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'ThirdNo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改时间（支付成功/失败的时间）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'UpdateTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'银行编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'BankId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'银行名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'BankName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'银行卡号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'CardNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'CreateUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'CreateUserName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ISQ请求时间(暂时不用)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'ISQRequestTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'转账最晚时间（请求时间+10天）(暂时不用)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'LatestTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'转账确认到款时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'ActualTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'转账确认到款人编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'VerifierId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'转账确认到款人姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic', @level2type=N'COLUMN',@level2name=N'VerifierName'
GO

EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'支付基础信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayBasic'
GO

CREATE TABLE [dbo].[PayLog](
	[PayOrderNo] [varchar](50) NOT NULL,
	[TotalAmount] [decimal](18, 4) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[AcceptType] [tinyint] NOT NULL,
	[OriginalString] [nvarchar](max) NOT NULL,
	[PayStatus] [nchar](10) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支付订单号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayLog', @level2type=N'COLUMN',@level2name=N'PayOrderNo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支付总金额' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayLog', @level2type=N'COLUMN',@level2name=N'TotalAmount'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayLog', @level2type=N'COLUMN',@level2name=N'CreateTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'接收接口类型(0-后台，1-前台,2-转账)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayLog', @level2type=N'COLUMN',@level2name=N'AcceptType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'第三方返回的原始字符串' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayLog', @level2type=N'COLUMN',@level2name=N'OriginalString'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支付状态(0-待支付；1-支付失败；2-支付成功。)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayLog', @level2type=N'COLUMN',@level2name=N'PayStatus'
GO

EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'支付日志表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PayLog'
GO

支付业务脚本.sql ，点击下载 (上边的脚本整理在一个文件中)