Solved

Credit card data, what datatype / length.

Posted on 2004-09-14
4
497 Views
Last Modified: 2013-11-18
Morning experts,  I want to update our old database fields we are using for credit cards.  Right now they are all varchar, but I wanted to find out what I should use for lengths and datatype for the following:

Card type (visa mastercard etc)
Card number
Experation date


0
Comment
Question by:moonrisesystems
  • 2
4 Comments
 
LVL 10

Expert Comment

by:AaronAbend
Comment Utility
for a card type I would use a char(1) and select letters that can be used to quickly identify the card (M for mastercard for example). easier than an int and virtually no difference in performance. Make sure it is NOT NULL. Use some other character for "unknown" if you must

Card number should still be a varchar. You save very little space with a char and can accommodate any card type.

Expiration date should be a date. Use the last date of the month/year (some cards actually have a complete date). If you do not do that and go with a char or varchar you will cause yourself a lot of trouble with date math.

Those are my recommendations. I have worked with credit cards though it has not been my focus. Others may have better experience.

Aaron
0
 
LVL 8

Accepted Solution

by:
MartinCMS earned 125 total points
Comment Utility
I have worked with database with credit cards info for more then 5 yrs.... thru many data type conversion before we have a stable set of data.  Here are my recommendations:

Card Type:   Varchar(20)     spell out the word Visa not just the V,M,A,etc.,
Card Number:   Varchar(20)
Card Expire:  Varchar(5)  mm/yy

you might want to consider the CVC or Card Code to.  This is the verification code from the back of the card.  Most Merchants required this number now.

CVC:  Varchar(5)

Good Luck!




0
 
LVL 7

Expert Comment

by:ChrisFretwell
Comment Utility
This site is good to explain the different card types, their lengths and check digits.

http://www.beachnet.com/~hstiles/cardtype.html

You may want to be careful about storing the CVV. Its purpose, wehn used, is to prove that the person making the transaction is in physical posession of the card. Its not on the magnetic stripe and not printed on receipts. This will mean reduced loss if somehow your system is comprimised. It can also reduce chargebacks if you require this number to be entered each time. This increases customer confidence etc. But those are marketing/business issues. Just thought I'd throw them in.
0
 
LVL 10

Expert Comment

by:AaronAbend
Comment Utility
If you store the year and month as a varchar, I would recommend formatting it YYMM (good for another 96 years at least) or YYYYMM so you can do easier queries if necessary. Depends on what you plan to do with the data of course (even just for validating the expiration has not been passed would be easier if it were in this format).
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 34
Shop sales Actual vs Forecast 2 24
How to convert JSON file to csv? 7 49
How to find duplicates in SQL Server 3 21
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Often people are aiming at development of perfect Magento websites. Though, it is easier said than done. You know what’s much easier? To ruin everything. It can be done in seconds. Many of us experimented with design, tried to change some values dir…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now