Field Types (Databases)

The field type in OpenOffice.org Base databases refer to the "kind" or "classification of" data that is required or accepted by each particular data field in a database record. For example, a field with the field type X will only accept data in X form while a Y type field will only accept data in Y form. If you enter otherwise, it will not be understood by the field and you will have a blank field or error.

Setting a particular field type for a field has its own consequences. For example, a field set to the type "Numeric" must have only numbers entered to its field. While this is good at keeping the values entered constant, it makes it inflexible since it doesn't have the facility to accept letters. If you want to add something more, like a measurement label for kilograms, milliseconds or meters, it will not be possible. Choosing the right field type for the right data for the database is crucial in making a database efficient and understandable.

There are several field types to choose from when making databases. They are as follows:

For characters or strings
- Text [varchar] - This is the default field type and is one of the most easiest to use. It basically sets the field to accept letters, numbers, punctuations and most other characters. Take note however that using this field, you cannot do arithmetic functions on the numbers.
- Text [varchar_ignorecase] - This field is similar to the type of field above. The only difference is that lower and uppercase entries are not considered when doing a search or Query.
- Text(fix) [char] - This field is also similar to the "text varchar" field. The difference is that the length of the field must be fixed to a certain amount of characters, and any blank spaces are saved differently in the database code for compatibility with other kinds of applications.
- Memo [longvarchar] - This field is used when you need the field to accept very long entries or comments. It is similar to the "text varchar" field. The only difference is that it can immediately accommodate entries with lengths over 2 billion spaces!
For whole numbers
- Tiny Integer [tinyint] - This field accepts integers or whole numbers with a fixed length of three (3) spaces.
- Small Integer [smallint] - This field accepts integers or whole numbers with a fixed length of five (5) spaces.
- Integer [int] - This field accepts integers or whole numbers with a fixed length of ten (10) spaces. It also has an auto value feature that fills up the field with a value you set automatically.
- Big Integer [bigint] - This field accepts integers or whole numbers with a fixed length of nineteen (19) spaces. It also has an auto value feature that fills up the field with a value you set automatically.
- Float [float] - This lets you enter "floating point numbers" which can represent whole numbers, or numbers with decimals. These kinds of fields can accept numbers more that 17 spaces long. This field is used by more professional users that deal with really large numbers or data like in programming.
- Real [real] - This field is similar to the "float" field and it can hold numbers with more than seventeen {17} spaces. It also stores the data in a way compatible with other database applications.
- Double [double] - This field is similar to the "float" field and it can hold numbers with more than seventeen {17} spaces, theoretically doubling the amount. It also stores the data in a way compatible with other database applications.
For fractions and decimals
- Numeric [numeric] - Field with this type can accept numbers, numbers with decimals, and it can even be set to handle fractions.
- Decimals [decimal] - This field is used primarilly for fields that need to handle nubers with decimal point. You can set the field to accept a number up to several decimal places.
Dates and Times
- Date [date] - This field type sets the field to accept data in a "date" format. There are many types of date formats, and you can choose from different kinds when you edit the field type. One of the most common forms is the "mm/dd/yy," where "m" represents the number of months, "d" the days and "y"the last two digits of the year.
- Time [time] - Using the time field type sets the field to accept only data in a time format. There are many types of time formats you can choose from. Typically though, the field should display the hour, minute and seconds of time separated by colons.
- Date/Time [timestamp] - This field is the combination of the "Date" and "Time" fields above. You also change the date and time formats used by editing the field.
For others
- Yes/No [boolean] - This field is set to handle a "Yes" or "No" input which is a common boolean argument.
- Image [longvarbinary] - This field is specifically designed if you want to put an image as an entry to the field. It is however dependent on the capability of OpenOffice.org Base if it can display or process the type of image entered in code.
- Binary [varbinary] - This field is set to handle any kind of binary "code" that you may want enter. This actually may include varied kinds of files, including encrypted ones if you like. It is dependent though on the capability of OpenOffice.org Base if it can display or process the code you entered.
- Binary(fix) [binary] - This is basically almost similar to the binary field above only that it should be set to have a fixed length in terms of spaces.
- Other [other] - This field is basically the all purpose "anything else" kind of field type. You can basically enter anything here including binary codes for images, sounds, files etc. Again like the other "binary" field types it all depends on Base if it can be presented correctly.

Using Database Field Types

The following screencasts demonstrate how to use database field types:

OpenOffice.org™ is a trademark of OpenOffice.org.