Of course the first thing to do before creating your tables is to decide on which fields you are going to need including and primary keys and extra keys that are going to help in the creation. As I am creating a table of books and their details I will probably want the following fields;
| Fields required | Field type |
|---|---|
| Bookname ISBN number Price Date purchased | String Number Number Date/time |
So we have now decided on which fields we want on what types they are, but does MySQL support the types that we want? Of course it does, we just have to decide which ones to use, lets have a look at all the different field types available in MySQL:
| Number type | Range |
|---|---|
| tinyint tinyint (unsigned) smallint smallint (unsigned) mediumint mediumint (unsigned) int int (unsigned) bigint bigint (unsigned) | -128 -> 127 0 -> 255 -31768 -> 32767 0 -> 65535 -8388608 -> 8388607 0 -> 16777215 -2147484648 -> 2147484647 0 -> 4294967295 -9223372036854775808 -> 9223372036854775807 0 -> 18446744073709551615 |
| Extra types | Size | Description |
|---|---|---|
| float double/real decimal/numeric date datetime timestamp year |
user defined user defined user defined YYYY-MM-DD YYYY-MM-DD HH:MM:SS user defined user defined |
Use to hold dates Use to hold date and time Another way to hold that date and time Use to store only the year |
| String types | Maximum number of characters |
|---|---|
| char varchar tinyblob/tinytext blob/text mediumblob/mediumtext longblob/longtext |
255 255 255 65535 16777215 429496295 |
| Other types | Description |
|---|---|
| enum set | Used when certain values are needed, eg "yes" or "no" or "maybe" Used when a number of certain values are needed |
All of these are listed in the MySQL manual with details on each.
So which ones do we want to use? Well lets look at the first one - 'Book name', well thats just a string, so we need a field type that can store strings, so whats the maximum length of a book name? well its not going to a `longblob/longtext' or a `mediumtext/mediumblob', but will it go over the length of 255 characters? probably not so we have now got 3 choices, `char', `varchar' or `tinyblob/tinytext'. Seeing as a bookname can be very different lengths I think that we should use a `varchar' for our `Book name' field as that way the database will automatically change the size of the data stored in the database and save us some space.
The next field is `ISBN number', well an ISBN number is in the form of "XXXXXXXXXX", meaning it has 10 characters, so we need a field type that can accomadate this number of characters, we also need to decide whether or not we want to include the `-'s between each set of numbers, but I don't think that these are necessary, if we had of wanted to include them we could not have used a number field and would have had to use a string field, however as we are going to use a number field I think we should use an unsigned bigint as this way we are assured of being accomadated for, as although an unsigned int would allow all 10 numbers, it has a maximum value of 4294967295 and hence, any ISBN numbers beginning higher than 4 would not fit.
Next we have `Price', what type is best for price? first instincts might go for a number, but price is in the form XX.XX, so we can not just use any number form, we need it to support decimal places, for `float', `double', `real' and `decimal' we can specify the size and number of decimal places that we want to be able to use so I would suggest one of these is used and due to the limitations of `float' and `double/real' I would suggest using `decimal/numeric' to show our prices, although this field type does actually store the values as a string I believe it is the best for our needs.
Our last field is `date/time' which is already accomodated for with `datetime' and `timestamp' but which one do we want to use? This all depeneds how you want to show the price once you have queried the database, if you just want a number then use `timestamp', but if your want the details formatted so to be readable by humans then I suggest using `datetime' as for 14/7/00, it would be displayed as `2000-07-14 00:00:00'.
So we have now decided on which field types we are going to use - whats the next step in creating our table?