The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database Design Question

I'm a newbie in the field of databases and I hit a roadblock: my database is going to be holding among other things, a table of customers who need to pay fees every month. The method of payment could be cash, credit card or check. If it's a check, we need the check number, .... If it's a credit card, we need credit card number, expiration date, approval number,....
Knowing that each method has its own set of unique attributes, how do I go about designing my payment tables and how do I link it to my customers table?
Thank you
DB Newbie Send private email
Sunday, November 05, 2006
 
 
I'm just a Java developer, but maybe something like:

- USER_INFO: All core details of the user
- CHARGE_CARDS: Attribute based (one card per row)
- CHECKING_ACCOUNTS: Attribute based
- PAYMENTS:
  * User Id
  * Description/Reason
  * Balance
  * Reoccurs (date)
  * Payment type (Credit, Check, Cash)
  * Payment value (Card Id, Checking Account Id)
- PAYMENT_HIST:
  * User Id
  * Payment Id
  * Payed (true/false)
  * Amount paid
  * Amount due
  * Payment date
... And then your business logic puts it all together.

I'm sure this could be further refined, though.
Manes
Sunday, November 05, 2006
 
 
If the information required for a cheque is different from the information required for a credit card, then use a separate table for each. In my long career I have tried it with single tables and separate tables, and separate tables have proved to be the best in the long run.
Tony Marston Send private email
Sunday, November 05, 2006
 
 
Each different entity should be in its own table.  Don't cut corners here and try and stuff "similar" but distinct data into the same attribute.  Utilize the power of JOIN's and VIEWS to provide access to the composite data (i.e. for reporting as needed.)  Also ensure that appropriate constraints are created to ensure that the database contains just the data you want.

Table: Payment
Columns:
  ID (Primary key)
  Date
  Amount
  CustomerID (Foreign key to parent table.)

  Candidate Key (Date, Amount, CustomerID)

Table: CreditCard
  Number (Primary Key)
  ExpirationDate
  SecurityCode

Table: CustomerCreditCard
  CustomerID (Foreign key to Customer table)
  Number (Foreign key to CreditCard table)
  Primary Key (CustomerID, CreditCardNumber)

Table: PaymentCreditCard
  PaymentID (Foreign key to Payment table.)
  Number (Foreign key to CreditCard table)
  AuthorizationNumber (Credit card transaction authorization number)
  TransactionDate (The date of the actual card transaction)
 

Table: PaymentCheck
  PaymentID
  Number (Check number)
  MICRCode (For scanned checks)
  Image (BLOB field for storing the image of the actual check)

Table: PaymentGiftCard
  PaymentID
  Number (Gift card ID number)
 

To view all payment information simply create a view which joins the Payment table against the PaymentCreditCard, PaymentCheck and PaymentGiftCard tables.

Without proper constraints in place, this scheme will allow for multiple payments from check/credit card/gift card against a single payment.  This is most likely an incorrect feature.  To ensure a 1 to 1 linking between the Payment table and one of the various Payment type tables would require some fancy database check constraints that do not exist in a lot of vendors DMBS's.  One method would be to only access those type tables through a stored procedure which would have all of the parameters needed and then depending on what data was available put the payment into the appropriate type table.  If DBMS vendors fully supported multi-table updateable views then live would be a lot easier and you could just utilize the view against all of the type tables as if it were a normal base table.

Good luck with your project!
ChattProgrammer Send private email
Sunday, November 05, 2006
 
 
Don't worry yourself with constraints. If you program it correctly your data will be correct. Don't use your database to enforce your programming logic. That's a DBA looking for a career.
onanon
Sunday, November 05, 2006
 
 
If the "security code" mentioned in the Chatt's credit card table is the printed numbers on the back, Visa's regulations forbid storing those even if encrypted.
http://usa.visa.com/business/accepting_visa/ops_risk_management/cisp_tools_faq.html
http://usa.visa.com/download/business/accepting_visa/ops_risk_management/visa_risk_management_guide_ecommerce.pdf
 
I strongly recommend spending some time reading this book:
http://www.amazon.com/exec/obidos/ASIN/0321320735/ref=nosim/librarything-20
Peter
Sunday, November 05, 2006
 
 
> Don't worry yourself with constraints. If you program it correctly your data will be correct.

I strongly disagree with this as it assumes:

- You will only ever have one application touching your data
- Applications that access the database will have no bugs
- Nobody will attempt to do maintenance of the data manually

Suer you don't *need* constraints - but they can save a lot of problems in the long term and they aren't that difficult to put in.
Arethuza Send private email
Sunday, November 05, 2006
 
 
>> Don't worry yourself with constraints. If you program it correctly your data will be correct.

Yes, we're all very familiar with those zero defect applications ... they're everywhere, I tell you.

Constraints are also an aid to query optimisation. A modern optimizer can do wonders with a complete set of constraints.

Personally I think that people who don't understand a technology should not be giving advice on it to others.
David Aldridge Send private email
Sunday, November 05, 2006
 
 
"Don't worry yourself with constraints. If you program it correctly your data will be correct. Don't use your database to enforce your programming logic. That's a DBA looking for a career."

Wow, not quite your strong point, huh?
D in PHX Send private email
Monday, November 06, 2006
 
 
You might want to do a little research before "charging in" to this.

https://www.pcisecuritystandards.org/pdfs/pci_dss_v1-1.pdf
Caffeinated Send private email
Monday, November 06, 2006
 
 
"Acquirers may be subject to fines of up to $500,000 per incident if a security breach is caused by a merchant or service provider who is not CISP compliant."
Caffeinated Send private email
Monday, November 06, 2006
 
 
I've seen so many developers doing complex or time-wasting things in code that could be accomplished with a simple sql statement, instead.

Whenever you can, rely on sql and the dbms! It is tested, it is optimized, it costs to much not to use it at full potential.
Sevenoaks Send private email
Tuesday, November 07, 2006
 
 
And here is an interesting presentation showing why DB security, stored procedures to prevent sql injection and strong named assemblies are important.
http://www.rockyh.net/AssemblyHijacking/AssemblyHijacking.html
Peter
Saturday, November 11, 2006
 
 
For a good general primer on how to design databases, check out Paul Litwin's excellent article "The Fundamentals of Relational Database Design." There's a copy, with his permission, on my old website (linked here), in the Developers' section.
JeremyNYC Send private email
Wednesday, November 15, 2006
 
 
"Don't worry yourself with constraints. If you program it correctly your data will be correct. Don't use your database to enforce your programming logic. That's a DBA looking for a career. "

Bad suggestion.
I'm a programmer. I'm not a DBA but I put all those things that I DBA does. You could make yourself or act like a DBA. Theres a lot of things you can do at the database level. Use the features that your application will benefit.
j2e
Thursday, November 23, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz