Database Table Data Types
Database Essentials - Part 3
Division 1
Forward: In this part of the series we look at database Data Types.
By: Chrysanthus Date Published: 4 Aug 2012
Introduction
Byte and Kilo
The length of a message is measured in bytes. One byte is one character. Kilo abbreviated K in computer software is 2 raise to the power 10.
Implementation
After designing the tables for a database, you need to code it (implement it) in a database management system. There are many DBMS. They come in different software packages such as Microsoft Access, Oracle, MSSQL, MySQL, Sybase, etc. The data types I describe to you in this tutorial are those of Microsoft Access. Those for the other packages are similar. When you are creating a table in a DBMS you have to specify the data type for each column in the table.
Text
This refers to phrase composed of letters and/numbers. This data type can be used for an address column for example. It can also be used for a column that has numbers that do not require calculations. Examples of sets of numbers that are not used for calculation are phone numbers and postal code. If you have a column in a table for phone numbers or postal code, its data type should be text. An example of a set of numbers that is used in calculation is money figures. The maximum size of the text data type is 255 bytes. So, if you declare a column as having a data type of text, none of its cell content should be longer than 255 bytes.
Memo
This data type is used for lengthy text (which may include numbers). You can use this data type for columns that have notes or descriptions. The maximum size for this data type is 64,000 characters.
This is a whole number data type. You use this data type for a column that has whole numbers. If you have a drinking bar for example, you may be interested in the number of bottles of drinks bought each day. You might have a database table, which has a column with the name, noOfDrinks; each row in such a table can represent a day’s information. So each table cell for the noOfDrinks column will have a whole number, for number of drinks sold on a day. An integer can be up to 2 bytes long.
Floating
The integer data type takes only whole numbers. A whole number does not have a fractional part. If you would use numbers with fractional parts then you need the Floating data type. The floating data type accepts numbers with fractional parts. However, the fractional part has to be in decimal. Examples of floating numbers are: 23.758, 100.4, and 589652.7454. This kind of numbers cannot be put in table cells whose column has been declared to have integers; you have to put them in cells whose column has been declared to have floating numbers.
Currency
Currency is a data type. This is for money. You can have a table column for money. There are many currencies in the world. Dollar and Euro are examples. The currency data type can be up to 8 bytes long. You may wonder why the floating data type is not used for currency. The reason is that money values are usually large (large whole number part) and need more precision. For example, accountants in the bank work with a lot of money number precision. They do not want any one penny to be unaccounted for.
Date/Time
Date/Time is a data type. An example of a date is 02-04-2009. There is more to the Date/Time data type. Read the documentation of the particular DBMS to know exactly how you have to type or input the date and/or time. The Date/Time data type can be up to 8 bytes long.
Boolean
With this data type a cell of a column can have either “true” or “false”. A data type is specified for a column (not for a row). Some DBMS may take “yes” for true and “no” for false or “on” for true and “off” for false. The Boolean data type is known to have a size of one bit.
This data type is typically used for a surrogate key that we saw in the previous part of the series. The difference between consecutive numbers in the cells of the column of this data type is 1 unit. Any of these numbers can be up to the size of 16 bytes.
BLOB
This data type is used for files. Have you ever imaging that files can be stored in a database? You can have one column in a table and each cell in the column will hold a file. What are files? You already know them. When you type a document in a word processor or text editor and save the document, the saved document is a file. Each image you see on a web page on the Internet is stored as a file in the hard disk of an Internet server. There are all kinds of files. Some files are executable files, in the sense that when you double click their icons within the operating system, they perform some task.
The BLOB data type is used to store any of these files; that is the BLOB data type is meant for storing files. It is the code of the file that is stored in a database table cell. The database itself cannot use the file. If the file is to be used, a copy of it has to be sent to some application, such as the one (word processor) that was used to create the file.
BLOB stands for Binary Large OBjects. This data type is used for storing files. If you declare a column of a database table to have the data type BLOB, then each cell of the column will be able to hold the code of a file.
Actually the BLOB data type is used for holding anything that is large in code form, which the DBMS does not really use. The DBMS can use numbers in a number column to find averages and totals, but is not expected to use the cell contents of the BLOB types to do anything.
For every table in the database there has to be a Table Column Design Worksheet. The Table Column Design Worksheet is not a table that holds data. It holds information about the data table itself. You will have to open the following link in your browser window tab to see an example (link below).
The Table Column Design Worksheet has rows. Each of the rows gives information about a column in the data table. In the browser window tab you have opened, Table 3.1 shows an employee table. The diagram below shows the Table Column Design Worksheet for the employee table. Employee table is the data table.
In the Table Column Design Worksheet there should be at least three columns. There is first off all the entity name and the table name. The entity name is just the name the users have for the table. The table name is the database table name, which is the name you will use in the implementation (coding). Both the entity and table name can be the same.
The first column in our Table Column Design Worksheet lists the data table column names. The second column gives the data type for each column in the data table. The third column gives the description of each column in the data table. For this third column, where the name of the column of the data table is explicit (e.g. LastName), the description is not given.
Table Column Design Worksheet does not have to be coded with the database during implementation. You can have it in a notebook (paper book) in your office. However, some people create separate database tables during the implementation phase in the DBMS for the Table Column Design Worksheets for each of the data tables of the database. Remember, the data tables, which are the database tables (e.g. employee) will be coded in the DBMS during implementation.
For the Table Column Design Worksheet of the employee table, only the AutoNumber and the Text data types feature. This is alright; all the data types do not have to feature in a Table Column Design Worksheet. The data types that feature, depend on the actual data table columns data. If we had something like a Date_Hired column in the employee table, then the Date/Time data type would have featured in the Table Column Design Worksheet. Pictures are stored in the computer in files: one picture as a file. If we had a column for employee pictures in the employee table, then the BLOB data type would have been used in the employee table. The employee table would have a column whose cells have the code files for each of the employee picture. The Table Column Design Worksheet would also have a corresponding row with the data type, BLOB. In the case of pictures, the user would need a program that would decode the code files for the pictures in order to see the pictures.
That is what I have for you for this tutorial. Let us end here and continue in the next part of the series.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT