Payments table design

Category: sql server dbdesign


slickk_F on Tue, 26 Feb 2013 23:47:10

please i need some help creating a table management.payments

create table management.payments
(paymentid int identity not null pk_paymentid,
patientid int not null constraint FK_patientid references patient.patientdetails(patientid),
paymentdate datetime check (paymentdate >= getdate() ) not null,
payment_method vachar check (paymentmethod in ('cash, cheque, credit card')),
cc_name varchar(40) allow null,
cc_number int allow null,
cheque_number int allow null,
final_payment money s
paymentstatus varchar check (paymentstatus in ('paid, pending')

im stuck where it tells me ;

the advancepayment should store the initial payment recieved at the time of admission,

final payment must be calculated by

finalpayment = totalbill -advancepayment

totalbill = wardcharges for the number of days spent , doctors fees and other applicable charges

thanks in advance 


Jackson_1990 on Wed, 27 Feb 2013 04:26:00


create table payments
(paymentid int identity not null constraint pk_paymentid primary key,
patientid int not null constraint FK_patientid foreign key references patient.patientdetails(patientid),
paymentdate datetime check (paymentdate >= getdate() ) not null,
payment_method varchar check (payment_method in ('cash', 'cheque', 'credit card')),
cc_name varchar(40) null,
cc_number int null,
cheque_number int null,
final_payment money,
paymentstatus varchar check (paymentstatus in ('paid', 'pending')));

scott_morris-ga on Wed, 27 Feb 2013 15:58:57

Start over.  Payments have relationships to entirely different entities - this would be something that would correspond to a vist or a bill or something along those lines.  Other problems:

  • you should not be storing credit card details (nor the security code) in this manner!!!!!! 
  • payment status is an attribute of this other entity mentioned above - not of an individual payment
  • do not assume that the effective date of a payment might be a date in the past.  Not everything is recorded on time.
  • payments can be made via check, monery order, cash, credit card, etc.  What does this fact imply? 
  • payments might come from different sources for a given bill/visit. 
  • if I bring all my children in for flu shots (including myself), at what level do you record the bill?  I can tell you that I will pay one amount for all services.

Louis Davidson on Wed, 27 Feb 2013 20:56:29

Adding to what has been said...  You need to start out by making a list of "things" that can occur, and then scrutinizing them.

1. A Payment. What does this mean? Is it the act of money changing hands, or the individual parts of a money changing hands.

A typical way to model this would be:

Payment (PaymentNumber (PK), PaymentDate, PaymentSequenceNumber, FinalPaymentFlag)
PaymentItem (PaymentNumber, ItemNumber, (PK PaymentNumber, ItemNumber), Amount, PaymentInstrumentId)

That allows you to split the payment for cash, credit, etc. Then you have to represent the payment method in some manner. Like was mentioned, you don't want to store the credit card number in a table like this for security reasons. So ideally, you charge their card, and then whatever is allowable (like the last four digits and type of a card, but research this with the security company.  Using a third party, you may just get back a token value that represents the card.  So the etc might represent a paymentInstrument... This I would subclass:

PaymentInstrument (PaymentInstrumentId, PaymentInstrumentType, BankToken?, AppliedToBillId)
CreditCardPaymentInstrument (PaymentInstrumentId PK, CreditCardIdentifier, expiration, whatever you can store)
CheckPaymentInstrument(PaymentInstrumentId, CheckNumber, BankName, etc)

This will allow you to deal with the paymentTypes individually (particularly if you have t charge them again, but also to allow expansion to PayPal, or EBT or something)

The matter of final payment could be on the payment, or you could build a table for paymentSchedule that you register payments against... Both of these types of solutions (either a fixed payment schedule, or a minimum payment with an open ended process) would require you to model the concept of the "bill", as to how much they need to pay inititally, and going forward.

Bill(BillId, BillNumber, PurposeOfBill, etc)
BillLineItems (BillId, LineNumber, LineItemCode, Amount)

But the main takeaway is to keep breaking down concepts until every table you have on your model means one thing. You can back away from a design for having too many tables that arent needed (usually because tables have a 1 to 1 relationship with each other), but you can never know too much about the true essense of your model you are trying to implement.

Kalman Toth on Sun, 03 Mar 2013 07:51:18

Any progress?  How about building tables as suggested by the above posts?

Here is a sample:

	PaymentID int identity(1,1) PRIMARY KEY,
	PatientID int not null references Patient.PatientDetails(patientid),
	PaymentTypeID tinyint references PaymentType,
	PaymentMethodID tinyint references PaymentMethod,
	PaymentStatusID tinyint references PaymentStatus,
	PaymentDate datetime CHECK (PaymentDate >= getdate() ) not null,
	Amount money not null,
	ModifiedDate datetime default getdate(),
	UNIQUE(PatientID, PaymentDate));

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012