or 02-9262-7770 
| An Introduction to Database Variable Types | |
By Chris Smith |
Published
01/10/2006
|
Programming
|
Rating:
|
|
|
Basic Data TypesWhy do we need to worry about the data types we're storing in a database? Well mainly because it helps you with database design. Did you know that the database itself can help to keep bad data out (for example you can't put 'text' into a number only field) and can speed things up especially searching and sorting? The easiest way to look at different data types is to look at some examples. For a product catalogue, you'll obviously need to keep a list of products (product categories, discounts, sales and so on are left out to keep this nice and simple). A products table will have: product id (our primary key) product name product description product price Product id is our "made up primary key" (see the previous tutorial for further details) since there's nothing else we can use to uniquely identify a database row. It's an integer (whole number) so the database can automatically increment the value when we add a new product to our list. Product Name will contain only a name so it will be relatively short. The name is a variable length (we don't know at this stage what sort of name you'll give a product or how long it will be). We can use a 'varchar' field. Varchar means "variable size character" and it can contain any data ('char'acters - numbers, spaces, letters, punctuation etc). With a 'varchar' field you have to tell the database the maximum size it will store. In this case we're going to limit it to 200 characters, so it becomes: varchar(200) Varchar fields will hold up to 255 characters, if you want to store more than that you have to use a different field type, such as blob or text. The product description might contain a lot of data (eg size of the box it comes in, bonus features for a dvd, minimum running specifications for a PC game etc etc). It probably won't be less than 255 characters so we can't use a varchar field. We can use a 'text' field however, which will hold up to 3-4 pages of text in mysql (in postgresql the 'text' field type doesn't have a size restriction). In mysql, if you need to store more than 4 pages, use the 'longtext' field type which will hold up to 32 pages in a single field. A product's price will be a number. However it might not be a whole number, so it can't be an integer. Both database systems (MySQL and PostgreSQL) support the 'float' data type but I recommend using something else. The 'float' type is not useful for data where significant numbers are essential. The float type could round numbers up or down to fit into it's setup, so read documentation carefully before using this particular type. Precision numbers (such as a product's price) will either need to use the 'decimal' type in mysql or 'numeric' type in postgresql, as these types do not round any numbers and will store the values exactly as you enter them. |
|
