Kexi/Handbook/Building Simple Databases/Designing Database Tables/The Table Designer window: Difference between revisions

    From KDE UserBase Wiki
    (→‎Designing the Persons table: + clarify that field name comes automatically)
    m (grammatical changes for readability)
     
    (3 intermediate revisions by 2 users not shown)
    Line 3: Line 3:
    ====The Table Designer window==== <!--T:1-->
    ====The Table Designer window==== <!--T:1-->


    =====Table Designer window consists of following columns:===== <!--T:2-->
    =====The Table Designer window consists of following columns:===== <!--T:2-->


    <!--T:3-->
    <!--T:3-->
    * Information icon - Additional Information about the field.  
    * {{Icon|help-about-desaturated}} - Additional Information about the field.


    <!--T:4-->
    <!--T:4-->
    * '''Field Caption''' - caption of the field which will be displayed during data entering.  
    * '''Field Caption''' - 'name' of the field which will be displayed during data entry.  


    <!--T:5-->
    <!--T:5-->
    * '''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.  
    * '''Data Type''' - a combo box shows a list of allowed data types, thereby setting a main rule for allowable data in the field. For example, when an integer number data type is set for a field, a user will not able to enter alpha characters into this field.  


    <!--T:6-->
    <!--T:6-->
    * '''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.  
    * '''Comments''' - you can enter here any information useful for clarifying the use of the field. This additional text will be saved within the table design and is only visible in design mode.  


    <!--T:7-->
    <!--T:7-->
    In the Table designer window, every row corresponds to a single table field. You can recognize you are in design mode because the <menuchoice>Design</menuchoice> button is toggled on within the Table designer window toolbar.  
    In the Table Designer window a row corresponds to a single field in the table. You can verify that you are in design mode because the <menuchoice>Design</menuchoice> button is toggled on (darkened) in the Table Designer window toolbar.  


    ===== Designing the Persons table===== <!--T:8-->
    ===== Designing the Persons table===== <!--T:8-->


    <!--T:9-->
    <!--T:9-->
    In the first row click on the cell in the '''Field Caption''' column and enter '''''Name''''' as field caption. Filling the '''Field Caption''' field automatically fills the '''Name''' field as seen in the '''Property Editor''' pane.
    In the first row click on the cell in the '''Field Caption''' column and enter '''''Name''''' as field caption. Filling the '''Field Caption''' field automatically fills in the '''Name''' field seen in the '''Property Editor''' pane on the right.


    <!--T:10-->
    <!--T:10-->
    Notes about field names and captions
    Notes about field names and captions<!--TODO: Clarify the relation between field name and caption-->


    <!--T:11-->
    <!--T:11-->
    * Every table field must have a name and a caption, these cannot be empty.  
    * Every field in the table must have a caption and thus a name. These cannot be empty.  


    <!--T:12-->
    <!--T:12-->
    * Field name is a word used by the database, usually not visible for users of the database application. The name may not contain special (national) characters (like ±, ¶, Ü) or space characters. The name must only contain roman letters, numbers and underscore sign "_". Use the latter instead of spaces or dashes.  
    * The field name is used within the database proper, and is usually not visible to users. The field name may not contain special (national or 'high order') characters (e.g. ±, ¶, Ü, %, &) or space characters. The field name may only contain roman letters, numbers and the underscore sign "_". Use the latter instead of spaces or dashes.  


    <!--T:13-->
    <!--T:13-->
    Line 41: Line 41:


    <!--T:15-->
    <!--T:15-->
    * Field caption, on the other hand, allows you to enter any letters and special characters. It will be displayed for users of the database application.  
    * The field caption, on the other hand, allows you to enter all letters and special characters. It will be displayed for users of the database application. Unsuitable characters entered into the field name will be filtered and replaced with the underscore for the corresponding field name automatically filled in on the right. The field name thus created can be edited to something else if preferred in accordance with the naming convention given above, but for the beginner we recommend the consistency of the automatically constructed field name.


    <!--T:16-->
    <!--T:16-->
    Line 59: Line 59:


    <!--T:21-->
    <!--T:21-->
    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 or press <keycap>F4</keycap>. You can also use the arrow keys <keycap>Up</keycap> and <keycap>Down</keycap> to select another type. The list of data types will appear. Select the '''Integer''' type.  
    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 or press <keycap>F4</keycap>. You can also use the arrow keys <keycap>Up</keycap> and <keycap>Down</keycap> to select another type. The list of data types will appear. Select the '''Integer''' type. <!--TODO: Arrow keys doesn't seem to work as claimed - the drop down list must be opened first!-->


    <!--T:22-->
    <!--T:22-->
    Line 68: Line 68:


    <!--T:24-->
    <!--T:24-->
    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.  
    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. <!--TODO: There are two dialogs at this point-->


    <!--T:25-->
    <!--T:25-->
    Line 74: Line 74:


    <!--T:26-->
    <!--T:26-->
    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 the contents of the Name field if you want to.  
    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 the contents of the '''Name''' field if you want to.  


    <!--T:27-->
    <!--T:27-->
    Line 80: Line 80:
    <translate>
    <translate>
    <!--T:30-->
    <!--T:30-->
    # The '''Persons''' table has been created and opened in '''Data View'''. Its name appears in the '''Project Navigator''' pane. </translate>
    # The '''''Persons''''' table has been created and opened in Data View. Its name appears in the '''Project Navigator''' pane. </translate>
    <translate>
    <translate>
    <!--T:31-->
    <!--T:31-->
    # Create the '''''phone''''' table, in a similar way as '''Persons''' table. </translate>
    # Create the '''''phone''''' table, in a similar way as '''''Persons''''' table. </translate>
    <translate>
    <translate>
    <!--T:32-->
    <!--T:32-->
    # Create a 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. </translate>
    # Create a '''''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. </translate>
    <translate>
    <translate>
    <!--T:33-->
    <!--T:33-->

    Latest revision as of 14:05, 1 May 2015

    Other languages:

    The Table Designer window

    The Table Designer window consists of following columns:
    • - Additional Information about the field.
    • Field Caption - 'name' of the field which will be displayed during data entry.
    • Data Type - a combo box shows a list of allowed data types, thereby setting a main rule for allowable data in the field. For example, when an integer number data type is set for a field, a user will not able to enter alpha characters into this field.
    • Comments - you can enter here any information useful for clarifying the use of the field. This additional text will be saved within the table design and is only visible in design mode.

    In the Table Designer window a row corresponds to a single field in the table. You can verify that you are in design mode because the Design button is toggled on (darkened) in the Table Designer window toolbar.

    Designing the Persons table

    In the first row click on the cell in the Field Caption column and enter Name as field caption. Filling the Field Caption field automatically fills in the Name field seen in the Property Editor pane on the right.

    Notes about field names and captions

    • Every field in the table must have a caption and thus a name. These cannot be empty.
    • The field name is used within the database proper, and is usually not visible to users. The field name may not contain special (national or 'high order') characters (e.g. ±, ¶, Ü, %, &) or space characters. The field name may only contain roman letters, numbers and the 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 field name Persons is the same as persons.
    • The field caption, on the other hand, allows you to enter all letters and special characters. It will be displayed for users of the database application. Unsuitable characters entered into the field name will be filtered and replaced with the underscore for the corresponding field name automatically filled in on the right. The field name thus created can be edited to something else if preferred in accordance with the naming convention given above, but for the beginner we recommend the consistency of the automatically constructed field name.

    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 or press F4. You can also use the arrow keys Up and Down to select another type. The list of data types will appear. Select the Integer type.

    From now on, the house_number field only accepts numbers.

    Persons table design is ready. Click the Data button on the toolbar to finish designing and switch to Data View for the table. This allows you to enter 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 the contents of the Name field if you want to.

    1. You are asked about an agreement for automatic adding of primary key to the table. Click Add primary key button to continue.
    2. The Persons table has been created and opened in Data View. Its name appears in the Project Navigator pane.
    3. Create the phone table, in a similar way as Persons table.
    4. Create a 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.
    5. Click the Data button on the toolbar and enter Phones caption for the table. As for your previous table, allow Kexi to automatically create a primary key.