支付信息与业务关联表,主要处理 合并支付 等 一对多关系的支付:
支付基础信息:
脚本如下:
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 ,点击下载 (上边的脚本整理在一个文件中)