Introduction
Creating the remote database
Creating the local database
OK, so now what?
Deciding on fields
Deciding on an index
Creating the table
Insert some data
Querying the database
Scripted querying
Uploading onto the server

Create a table containing all necessary fields

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 that's just a string, so we need a field type that can store strings, so what's 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 accommodate 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 accommodated 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 accommodated for with `datetime' and `timestamp' but which one do we want to use? This all depends 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 - what's the next step in creating our table?