July 22, 2006 at 6:29 am
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 11:35 am
The difficult part is not knowing your intended design and whether or not you have gone far enough into the thought process. Have you sat down and done a paper design of the whole system?
July 25, 2006 at 12:13 pm
Hi,
when we add the value for Cheque Payment then they gave me a There 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
July 25, 2006 at 1:58 pm
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)
You MUST add the record in PaymentTypes to be able to insert in
CashPayment (paymentID is foreign key FK_PaymentTypes_CashPayment)
Anyway DID you examined if your structure allows you to retrieve data corectly?
What if a client has more than 1 number? what do you do ? he can t pay them all in one payment?
Vasc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply