What is the best data type for ISBN10 and ISBN13 in a MySQL database

Question

For an application I'm currently building I need a database to store books. The schema of the books table should contain the following attributes:

id, isbn10, isbn13, title, summary

What data types should I use for ISBN10 and ISBN13? My first thoughts where a biginteger but I've read some unsubstantiated comments that say I should use a varchar.

Lennyleno

how is the data going to be used? - if you're not going to be carrying out mathematical operations on it, there's no issue storing the data as a VARCHAR or similar. – Tartuffe

can't use an int, since that'd only cover ~20% of what an isbn could be (0->2 billion v.s 0->10 billion) – Hallett

I'm only using the ISBN numbers to search/query books in the database. – Lennyleno

Answer 1

You'll want a CHAR/VARCHAR (CHAR is probably the best choice, as you know the length - 10 and 13 characters). Numeric types like INTEGER will remove leading zeroes in ISBNs like 0-684-84328-5.

Evacuate

You will also want to strip hyphens and whitespace before inserting into the database so that it will fit in 10 or 13 characters, and to make search/querying easier. – Muncy

Answer 2

ISBN numbers should be stored as strings, varchar(17) for instance.

You need 17 characters for ISBN13, 13 numbers plus the hyphens, and 13 characters for ISBN10, 10 numbers plus hyphens.

ISBN10

ISBN10 numbers, though called "numbers", may contain the letter X. The last number in an ISBN number is a check digit that spans from 0-10, and 10 is represented as X. Plus, they might begin with a double 0, such as 0062472100, and as a numeric format, it might get the leading 00 removed once stored.

84-7844-453-X is a valid ISBN10 number, in which 84 means Spain, 7844 is the publisher's number, 453 is the book number and X (i.e 10) is the control digit. If we remove the hyphens we mix publisher with book id. Is it really important? Depending on the use you'll give to that number. Bibliographic researchers (I've found myself in that situation) might need it for many reasons that I won't go into here, since it has nothing to do with storing data. I would advise against removing hyphens, but the truth is everyone does it.

ISBN13

ISBN13 faces the same issues regarding meaning, in that, with the hyphens you get 4 blocks of meaningful data, without them, language, publisher and book id would become lost.

Nevertheless, the control digit will only be 0-9, there will never be a letter. But should you feel tempted to only store isbn13 numbers (since ISBN10 can automatically and without fail be upgraded to ISBN13), and use int for that matter, you could run into some issues in the future. All ISBN13 numbers begin with 978 or 979, but in the future some 078 might could be added.

• A light explanation about ISBN13
• A deeper explanation of ISBN numbers

Axillary