Designing Database Tables
Prev
Next

Designing Database Tables

First, there will be two tables added to your database: persons and phone_numbers. These are exactly the same tables as described in chapter Database and spreadsheet. A layout for Persons can be found in section Data integrity and validity in that chapter.

The Table Designer window

Table Designer window consists of following columns:

  • PK - Primary Key.

  • Field Name - field name (in other words: column name) which will be visible during data entering.

  • Data Type - a combo box containing a list of data types, allowing to set a main rule for entered data for a given field. For example, when an integer number data type is set for a field, a database user will not able to enter letter characters into this field.

  • Comments - you can enter here any information useful for understanding what the given field is provided for. This additional text will be saved within the table design and only visible in design mode.

In the Table designer window, every row corresponds to a single table field. You can recognize you are in design mode because the Switch to Design View mode button is toggled on within the main Kexi toolbar.

To start entering the Persons table design:

  • In the first row click on the cell in the Field name column and enter name as field name.

    Notes about field names

    • Every field must have a name, it cannot be empty.

    • Field names may not contain special characters (like ±, ¶, Ü) or space characters. The names must only contain letters, numbers and underscore sign "_". Use the latter instead of spaces or dashes.

    • Field names must be started with a letter or underscore sign "_", never with a number.

    • It does not matter whether you are using small or capital letters. For Kexi the database name "Persons" is the same as "persons".

  • In a similar way, enter the following fields into the table design:

    • surname

    • street

    • house_number

    • city

  • All the above fields, except house_number, are of type text. Change house_number field's type to integer number. To do this, click on a cell in the Data Type column, house_number row and then click on drop down list's button (you can also press F4 or Alt+Down. The list of data types will appear. Select the Integer number type.

    From now on, the house_number field only accepts numbers.

  • Persons table desgin is ready. Click Switch to Data View button on the toolbar to finish designing and switch to Data View for the table. This allows you entering data into the table.

  • As the design is not yet saved in the database, the "Save Object As" dialog window appears. You need to specify the name for the new table.

    Kexi offers a generic name like Table1. To change the name, enter Persons into the Caption field and press the Enter key or click the OK button. The Caption field will be used to display the table to database end-users, e.g. as a form. Unlike the name, the caption can contain any characters including spaces and special characters.

    Note that filling the Caption field automatically fills the Name field. For your convenience the rule for using only letters, numbers and the "_" character is kept. You can alter contents of the Name field if you want to.

  • You are asked about an agreement for automatic adding of primary key to the table. Click Add primary key button to continue.

  • The Persons table has been created and opened in Data View. Its name appears in the Project Navigator pane.

  • Create phone_numbers table, in a similar way as persons table.

  • Create person field of type Integer number and phone of type Text. Do not use a number type here because phone numbers can have many different forms and prefixes.

  • Click Switch to Data View button on the toolbar and enter Phones caption for the table. As for your previous table, allow Kexi to automatically create primary key.

Prev
Next
Home


Would you like to make a comment or contribute an update to this page?
Send feedback to the KDE Docs Team