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 fieldsOf 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;
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:
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? |