Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am modeling a "pizza place" and a customer has an account info. I created a separate table for account info. In here a customer will have an amount spent and method of payment attributes. The payment methods can be cash, cheque or credit card. If it is credit card, it needs to have a number. I'm wondering how to best represent this attribute. Does it need to be another table.

share|improve this question
    
Are you storing the full credit card number in the database? –  Nick Chammas Mar 20 '12 at 17:29
    
You should read this document before progressing too much further with your design. –  ConcernedOfTunbridgeWells Mar 20 '12 at 17:33
    
Specifically, page 8. –  Nick Chammas Mar 20 '12 at 17:42
    
And section 6.4. –  ConcernedOfTunbridgeWells Mar 20 '12 at 17:53
add comment

1 Answer

Credit card info should be in a separate table because normally, you need to have more than just a number. You will also need, at the very least, the card's expiry date. You may also need to store cardholder information (name, address, etc...) in case it is different from the name on the account. All of that extra information is only relevant to customers that pay by credit card. And will you allow multiple cards for one account? How will you handle cards that expire and need to be replaced? Delete the old record, mark as expired, add a new one? Or just update existing data?

And although it's not necessarily going to be reflected in an ER diagram, you have to consider security if you plan to implement this. You might want to start looking here: https://www.pcisecuritystandards.org/

share|improve this answer
    
This is for a college project design. I'm not sure if I need to enforce strict security standards. –  devcoder Mar 20 '12 at 19:17
1  
@JohnSmith: For course work, probably not (unless the instructor hints at it in the assignment paper). You could make mention of it, or explain how you might encrypt the card numbers/expiry dates if you store complete card numbers. –  FrustratedWithFormsDesigner Mar 20 '12 at 19:19
    
In response to the other part of your answer - I am going to assume that each person has only one card. So I'm thinking I can store all the info related to the card in a separate table and have an id for the card as unique id, which would be stored as the payment method in the account info table. For people who don't pay with card, I could just put in "cash" or "cheque". Am I thinking right ? –  devcoder Mar 20 '12 at 19:24
    
I would keep the payment method (cash, card, etc...) as separate from the field that stores any reference to a card. –  FrustratedWithFormsDesigner Mar 20 '12 at 19:29
1  
@JohnSmith: I could but then I'd want the class credit for it! :P –  FrustratedWithFormsDesigner Mar 20 '12 at 19:47
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.