July 22, 2006 at 9:06 pm
Hi
I am new for making the database in sql server 2000 .Actually we are build a Point Of Sale System for mobile Phones. it is a online transactions.
Here it is my database tables with attributes that we are use for payment process.
Payment : (referenceNo,invoicePaymentId,securityPaymentId,billPaymentId,
DealerId, customerId,branchId, payCode , mobileNo)
Customer: (customerId, branchId, mobileNo, customerName, customerAddress)
Dealer: (dealerId, branchId, dealerName)
Invoice Payment: (invoicePaymentId, invoice Date, InvoiceAmount, invoice Balance)
Bill Payment: (securityPaymentId, current Deposit, additional Deposit, amount, security Balance)
Payment Types: (reference No , PaymentId)
Cash Payment: (paymentId, cashPaymentDate, cashReceiptsNo)
CreditCardPayment: (paymentId, expiration Date, card Name, authorization Date)
ChequePayment: (paymentId, locOut, bank Name, branch Name)
Customer Primary keys relate to the Payment table as a foreign key .
InvoicePaymentId is a PK in Invoice payment table and they relate to the Payment table as a foreign key .
And Bill Payment, Security Payment same as relate to the Payment Table as a foreign key.
But
Payment table have a primary key reference No and they are relate to the Payment Types table as a Campsite key .
And CashPayment, ChequePayment, CreditCardPayment Tables are relate to the PaymentTypes table as a Foreign Key.
Please Tell me is it Correct Relation?
But when we run this store procedure then they gave me a ERROR .Tell me what is Better for Payment .
If you gave me some Idea Or gave me a Schema For payment Process I am thankful.
July 25, 2006 at 8:00 am
This was removed by the editor as SPAM
July 25, 2006 at 9:20 am
Payment : (referenceNo,invoicePaymentId,securityPaymentId,billPaymentId,
DealerId, customerId,branchId, payCode , mobileNo)
1 payment can serve many mobileNo (companies for example)
Invoice Payment: (invoicePaymentId, invoice Date, InvoiceAmount, invoice Balance)
an invoice should be connected to a client (how do I see what a client has to pay? )
Vasc
July 25, 2006 at 9:48 pm
Hi,
when we add the value for Cheque Payment in Query analyzer then they gave me a these ERROR.
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 547, Level 16, State 1, Procedure SP_InsertNewInvoice, Line 45
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_PaymentTypes_CashPayment'. The conflict occurred in database 'CDRS', table 'CashPayment', column 'paymentId'.
The statement has been terminated.
(1 row(s) affected)
Stored Procedure: CDRS.dbo.SP_InsertNewInvoice
Return Code = -6
Here it StoreProcedure:
CREATE PROCEDURE dbo.SP_InsertNewInvoice
@mobileNo bigInt,
@invoicePaymentId int,
@customerName nvarchar(50),
@invoiceAmount decimal,
@invoiceBalance decimal,
@invoicePaymentDate DateTime,
@payCode nvarchar(50),
@referenceNo nvarchar(50),
@locOut nvarchar(50) = NULL,
@bankName nvarchar(50) = NULL,
@branchName nvarchar(50) = NULL,
@cardName nvarchar(50) = NULL,
@expirationDate DateTime = NULL,
@authorizationCode nvarchar(50) = NULL
as
Begin
Declare @customerId nvarchar(50)
Select @customerId = customerId From Customer where customerName = @customerName AND customer.mobileNo = @mobileNo
INSERT INTO InvoicePayment(invoicePaymentId, invoiceAmount, invoiceBalance, invoicePaymentDate)
VALUES (@invoicePaymentId, @invoiceAmount, @invoiceBalance, @invoicePaymentDate)
INSERT INTO Payment( customerId, invoicePaymentId, payCode, referenceNo)
VALUES (@customerId, @invoicePaymentId, @payCode, @referenceNo)
INSERT INTO paymentTypes(paymentId, payCode,referenceNo)
VALUES (@invoicePaymentId, @payCode ,@referenceNo)
If @payCode = 'Cheque'
INSERT INTO ChequePayment(paymentId, locOut, bankName, branchName)
VALUES(@invoicePaymentId, @locOut, @bankName, @branchName)
Else If @payCode = 'CreditCard'
INSERT INTO CreditCardPayment(paymentId, authorizationCode, expirationDate, cardName)
VALUES(@invoicePaymentId, @authorizationCode, @expirationDate, @cardName)
Else
INSERT INTO CashPayment(paymentId, cashReceiptNo)
VALUES(@invoicePaymentId, @referenceNo)
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
So Please tell me Where i am Wrong and what to do for solve this problem??
July 26, 2006 at 2:05 am
Please will you post the SQL for creating the constraint FK_PaymentTypes_CashPayment.
Thanks
John
July 26, 2006 at 3:28 am
hi
here it is Create Procedure
CREATE TABLE [PaymentTypes] (
[referenceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[payCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[paymentId] [int] NOT NULL ,
CONSTRAINT [PK_PaymentTypes] PRIMARY KEY CLUSTERED
(
[referenceNo],
[payCode],
[paymentId]
) ON [PRIMARY] ,
CONSTRAINT [FK_PaymentTypes_CashPayment] FOREIGN KEY
(
[paymentId]
) REFERENCES [CashPayment] (
[paymentId]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_PaymentTypes_ChequePayment] FOREIGN KEY
(
[paymentId]
) REFERENCES [ChequePayment] (
[paymentId]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_PaymentTypes_CreditCardPayment] FOREIGN KEY
(
[paymentId]
) REFERENCES [CreditCardPayment] (
[paymentId]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_PaymentTypes_Payment] FOREIGN KEY
(
[referenceNo],
[payCode]
) REFERENCES [Payment] (
[referenceNo],
[payCode]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [CashPayment] (
[paymentId] [int] NOT NULL ,
[cashPaymentDate] [datetime] NOT NULL ,
[cashReceiptNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_CashPayment] PRIMARY KEY CLUSTERED
(
[paymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [ChequePayment] (
[paymentId] [int] NOT NULL ,
[locOut] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[bankName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[branchName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_ChequePayment] PRIMARY KEY CLUSTERED
(
[paymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CreditCardPayment] (
[paymentId] [int] NOT NULL ,
[expirationDate] [datetime] NOT NULL ,
[cardName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[authorizationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_CreditCardPayment] PRIMARY KEY CLUSTERED
(
[paymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
July 26, 2006 at 4:31 am
I don't think this can work with your current database design. It seems to me that where you have gone wrong is to create your FK constraints the wrong way round. So, instead of having the following constraint in your PaymentTypes table:
...CONSTRAINT [FK_PaymentTypes_CreditCardPayment] FOREIGN KEY (paymentId) REFERENCES CreditCardPayment (paymentId)...
You should remove this constraint and create it the other way round in the CreditCardPayment table:
ALTER TABLE CreditCardPayment ADD CONSTRAINT FK_CreditCardPayment_PaymentTypes FOREIGN KEY (PaymentID) REFERENCES PaymentTypes(PaymentID)...
One further piece of advice. Give your tables names that accurately describe what they are. To me, PaymentTypes sounds as if it should be a lookup table that holds values such as 'Cash', 'Credit Card', 'Cheque', 'Direct Debit' and so on. Maybe renaming it Payments would be appropriate. And decide whether you are going to name your tables in the singular or plural and stick with it. This will all make it easier for someone else to guess what is going on.
Hope that helps
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply