info
Back to overview

What is a Field in a Database?

What is a field in a database?

Databases are a key tool in managing businesses. Whether you’re using simple handwritten notes, Baserow, Excel, Access or another option, there are going to be elements of a database in your work. Even keeping a simple list of your customers on a notepad is a rudimental type of database.

So when using a database, you need to know what certain terms are. When you understand what different parts of the database are, you can learn to manage them better. Then you can manipulate the data that is contained within the database to make important business decisions. One question that you might want to ask is ‘what is a field in a database?

What is a Field in a Database?

In simple terms, a database consists of tables. Each table consists of numerous table cells, which are important snippets of information. These table cells are arranged into columns and rows. The rows are the records. This is where information for a certain object (i.e. customer, order, etc.) are kept. The columns are the fields. The field will correspond to certain data like ID, colour and postcode.

Screenshot table field/column and record/row

For instance, if you have a record for a customer that contains their name, address and telephone number, each of those pieces of information are considered a field. The collection of data within the fields along one row create a record.

On their own, a field is rather inconclusive. No conclusion can be made from a list of colours without other fields that give context to the data presented. but a collection of fields can be a powerful tool. For instance, a field that reads colours and lists red, green, yellow in the table cells mean nothing if you don’t know what the colours represent or are connected to.

What can be Included within a Field on a Database?

A field can store lots of different types of data, but importantly they can only store one type of information and not a mix. Standard databases offer many different types of fields: number, text, files, images, web addresses and more.

High-quality media images and videos can easily be added to a database. Baserow allows you to upload this images, videos, documents and any other elements to a field with a simple drag and drop feature. This allows you to create a better database quickly.

What is an ID Field in a Database?

An ID field is a unique identifier that allows you to distinguish between different records on a database. Some classic examples of this would be customer, product and orders ids.. The reason why there are unique identifiers is because some details may be similar.

For instance, you could have three customers called John Smith or dozens of products that could be called Frozen Peas. An ID field allows for records with similar values in other fields to not be confused.

This ID field can also be used when you want to create relational databases where data can be retrieved from another table to automatically insert data into other fields. For instance, if you have a database of orders and each order has the customer ID inserted, that customer ID can be linked to a customer database and the shipping data retrieved automatically inserted into the orders table. This can save you time having to manually enter this data.

Do all Fields in a Database have to be Manually Entered?

When it comes to creating databases, many business owners are put off by thinking that it involves a lot of data entry. However, Baserow allows you to automatically retrieve and populate databases using APIs. If you have access to developers, they can automate this for you. If you don’t have access to technical expertise, then web-hooks can populate tables with ease. In addition, data can be imported from spreadsheets, json and xml files. And you can reduce the time it takes to enter data with certain fields that have multi/single select, date or boolean requirements as there are specialised input widgets.

The single select field option is where you are presented with a set of values and you can only select one from the list of preset options. A classic example could be colours like ‘Red’, ‘Blue’ and ‘Yellow’. The person entering data can then select one of these options to become the field data value.

The multiple select field works in much the same way. However, the person entering the data can select more than one value to be added to the field. This doesn’t create two fields, but rather a field with two entries in it.

Another advantage of a multiple select field is that it can save time when edits need to be made. For example, if you have a multiple select field that contains ‘red’, ‘blue’ and ‘yellow’ but decide that ‘yellow’ should really need to be ‘green’, you only need to change this once. As soon as the change has been made, all the records that reference ‘yellow’ will be changed to ‘green’. You don’t need to re-enter the values yourself. This can save you lots of time, especially if you’ve already got hundreds of records with ‘yellow’ as a value in the field.

The multiple select field does not restrict the analysis that's available. For instance, if you wanted to find all the records that have red in them, you can create a database query that looks for ‘red’ in the field. The query would display all those that have red on their own, but also all the records that included red with another colour.

This is where boolean searches can be used. A Boolean search is a special type of search where users can combine keywords with certain operators. For instance, someone wanting to find all the records with red or blue will do a search for ‘Red OR Blue’. However, if they wanted to find all the records with Red but not include any in the search that included Blue as well, the search would be ‘Red NOT Blue’.

What is a Lookup Field in a Database?

There are other types of fields, other than the basics that have been mentioned above that can be really useful. For instance, you can use a lookup field. This is a field within the table that is retrieved from another table or query.

The classic example of this would be an orders database. If on one table, the customer number is the identifying field for who made the order, other information about the customer, like name, address, telephone number, etc. can be collected from a customer database.

The data is easily retrieved by looking for the customer ID within the customer table and then retrieving the right information and populating the order table with the correct information. This is another way that time can be saved when you’re creating a database. This can also create table relationships that make databases more dynamic and useful.

Do all Fields Need Data to be Valid?

There are times when you might have fields, like mobile number or email address, where you don’t always have that information. For instance, a customer might not have an email address or a mobile/landline. Therefore, while your database may have those fields within a table, you can’t always enter them.

With most databases, an empty field does not invalidate the record or the database. There are two options when managing your data in this case. The first is to leave the field empty. It is a simple solution, but there are times when you might be entering information on the next record and accidentally enter information in the empty field.

Another option is to use default values. This could be something simple like a ‘0’ or ‘not provided’. These simple additions can then also be used in queries when you want to find records that have missing fields.

How to Ensure all Fields have Correct and Valid Inputs

One of the more complex elements of managing databases is that you need accurate information. This isn’t just for ensuring that you have the best data for analysis, but also that you comply with laws. Privacy laws state that all information must be accurate and companies face fines if they’re not.

When data entry is taking place, there is always the chance of an error being made. Research has found that some manual data entry can have an error rate of around 2.8%. That means there is an error for every 50 data values that are entered into a database. When you can have 10 or more fields within a table, that equates to one in every five records (20%) having wrong information entered.

There are ways to minimise data entry mistakes. The first is to use some of the more complex functions that you can find in Baserow. For instance, using single select and multiple select fields can help to reduce the number of errors made. And when you have relationships between tables, you can reduce the errors through lookup fields.

Another option is to put restrictions on the type of data that can be entered within a field. Many errors are simple typos where a number or letter has been entered when that field shouldn’t have them. For instance, telephone numbers shouldn’t have any letters within, but sometimes letters on the top row of the keyboard can be entered. A classic example would be special characters (i.e. +,#,_, etc.) entered in data values for a telephone number field. By having specific limitations (i.e. numbers) set for that field, you can reduce the chance of this happening.

When using a field with restrictions, like a phone number only field, entering incorrectly formatted data can show a warning to the user in some database software.

What are Fixed Length and Variable Length Fields?

Another way to ensure that some fields are valid is to ensure that fields have certain lengths to them. This is where you might want to use a fixed-length field. Within this field, you will want to add a value for the number of characters that must be entered. If the value is not met, then the user will be told to enter the correct data.

A variable-length field is a typical field that allows data of any length to be entered. When you’re dealing with data like name, address, etc. you should use variable-length fields.

How can Fields help Manage your Business?

There are many ways that fields in a database can help you manage your business. While on their own they’re not powerful, they can be sorted and used as a way to collect data. For instance, the date field can be used to collect orders that were made between two specific time periods, or on a specific date. You can use customer IDs to find all the orders made by a particular customer.

Or you can use a database to see which promotions have helped drive business by showing which sales included certain discount codes, affiliate links and more.

Using this, you can make important decisions that can reduce costs, improve revenues and grow your profit margins. At the same time, you can find that using Baserow can save you time.

If you would like to know more about here, then you can find out more here.

release
November 24, 2021 by Bram Wiepjes
November 2021 release of Baserow

The release of version 1.7 brings us the lookup field type, table webhooks, a kanban view, premium licensing system and other bug fixes and improvements.

info
March 2, 2021 by Bram Wiepjes
Best Excel alternatives
info
May 22, 2020 by Bram Wiepjes
Best Airtable alternatives