Tables

A database is made up of several components, of which the table is most significant. A table is a collection of related data held in a structured format within a database. All data in a database is stored in one or more tables which puts tables at the heart of any database. A table to store a list of books in a library is shown in the image below.

Table properties

Table Name

The table name describes the type of data stored. For example a table that stores information about books in a library would simple be named Books. It’s a good practice to name the table using the plural form (Books instead of Book). Tables are organized into vertical columns and horizontal rows.

Table fields

Every book in our library has some common attributes or properties. For example the book title, author and ISBN number. These attributes are called the table fields and represent the columns of our table.

Field Data Types

Each field in a table must have a field name and a data type. The data type depends on the information this field will store. The most basic field types are listed below:

• Text. This is the most common data type. It's used for storing text information such as name, addresses, descriptions etc.
• Integer. For integer numbers. For example 20 232 1003.
• Decimal. Numbers with decimal digits or real numbers. For example  0.6 34.34234 3.134545.
• Date or Time. For storing date and time information. For example 19/02/2017 18:22:10.
• Boolean. This type has can can have the "True" or "False" value.

Records

A record is a unit of information inside a table. For example, to store a particular book in our Books Table we insert a new record (or row) and fill all the fields with data. Notice how each record spans across all table fields.

Primary keys

In the world of databases, the primary key of a relational table uniquely identifies each record in the table. Databases use keys to compare, sort, and store records, and to create relationships between records. In the books Table the primary key is the ID field and for every book the ID is unique meaning that two ore more books can not have the same ID. A table in Base must always contain a primary key.

Creating a Table

You can create a Table in Base using either the Design View or the Create Table Wizard. It is recommended to use the Design View to better understand the table concept in Databases. To create a table:

1. Select the table object type
2. Create Table in Design View from the Tasks window.

Table Design View

In Table Design View you create a table by entering all the table fields information (field name, data type and description). For each field you can also specify some Field Properties.

Always enter first the primary key for your table. The primary key must be of type Integer and have the AutoValue property enabled. This means that Base will fill this value for us whenever we create a record. This is a good practice because we avoid entering duplicate primary keys in our table.

Next enter the remaining fields of your table

For each field you can specify some properties

• Entry required. If set to Yes a value must always be filled a record for this field.
• Length. The length can be set to a maximum value to to constrain the size of a numerical or text field.
• Default value. A field can have a default value
• Format. For numerical, date and time values you can set the display format of the value.

Saving a table

To save your table click on the save button and  give the table a name. The table now appears in the Tables list.

Database Object Toolbar

Once you create any database object if you select it from the Objects List the Database Objects toolbar is activated. Using the toolbar you can open, edit, delete or rename the object.

• Open. Opens the Table to enter data.
• Edit. Opens the Table Design View to edit table fields and properties
• Delete. Deletes the tabla
• Rename. Renames the table

Entering data

To start entering data in a Table, select the table and click on the Open Button , or double click on the table in Objects List. When opening a table this way the Table Data View window appears.

In Table Data View you can enter new records by typing the values in each row. If you switch the Edit Mode to Off then you can only view the data. In this window you can also sort and filter the table data. When done click the Save button to save your changes.