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.
-
Are you storing the full credit card number in the database?– Nick ChammasMar 20, 2012 at 17:29
-
You should read this document before progressing too much further with your design.– ConcernedOfTunbridgeWellsMar 20, 2012 at 17:33
-
Specifically, page 8.– Nick ChammasMar 20, 2012 at 17:42
-
And section 6.4.– ConcernedOfTunbridgeWellsMar 20, 2012 at 17:53
Add a 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/
answered Mar 20, 2012 at 17:37
-
This is for a college project design. I'm not sure if I need to enforce strict security standards.– devcoderMar 20, 2012 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. Mar 20, 2012 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 ?– devcoderMar 20, 2012 at 19:24
-
I would keep the payment method (cash, card, etc...) as separate from the field that stores any reference to a card. Mar 20, 2012 at 19:29
-
1@JohnSmith: I could but then I'd want the class credit for it! :P Mar 20, 2012 at 19:47