CCNC/CCNC Module 5/Designing and creating tables/Basic Table Operations/Design A Table

---

Design a Table

Outcomes

Upon completion of this tutorial the learner will be able to: Add attributes to a Table Create a Primary Key for the Table Identify and define required fields Save the new table

Adding Fields to your Table

Figure 1. Designing a Table

Once in design view you can add fields to the table. This is when it can be useful to have designed the tables using pencil and paper and then transpose the design into the computer. You add fields by selecting a row (starting at the top) in the design window. The active row is indicated by the green arrow head, this is where the new field will be added. In the 'Field Name' field enter the name you want for the attribute. It is strongly recommended that you do NOT use spaces in your field names. In the next column, 'Field Type', select the type of field best suited for the data you will be entering into the field. Continue adding fields until all the required attributes have been added to the table.

Tip: Use underscore characters in the field names

• It is recommended that spaces are NOT in field names as the spaces can become problematic when referencing the fields in queries, forms and reports. In some database systems using spaces causes an error when creating a table. The most common method for including a space in a field name is to use the underscore character; the field 'first name' becomes 'first_name'.

(: Remember the database wording;

• the words table and entity are interchangeable
• the words field and attribute are interchangeable

)

Adding a Primary Key

Figure 2. Defining a Primary Key

As discussed in the previous section, it is important to define a primary key so duplicate values are not entered into a table. To set a field to the primary key, click to the left of the field in design view and select 'Primary Key'. A small icon looking like a key will appear next the the field name.

Setting required fields

Figure 3. Required fields

When designing a table some of the fields (or attributes) must contain data for every new record added to the database table to be accurate. When designing the table it is possible to set fields to be mandatory fields. On the bottom half of the table design window is a section called 'Field Properties', in the section there will be the ability to set an 'Entry required' property to either 'Yes' or 'No'. This will force the field to have data when a new record is being added to the table. It is also a good idea to add descriptions to your fields, this will help to document your table designs for later maintenance.

Saving the new table

Figure 4. Save Menu

To save the new table, go to the File menu and select Save. If the table has never been saved before you will be prompted to enter a table name. It is also strongly recommended that you do NOT enter spaces in the table name. The reasons for not having spaces in the table name are the same as for no spaces in the field name.

Tip: Use CamelCase in the table names

• It is recommended that spaces are NOT in table names as the spaces can become problematic when referencing the tables in queries, forms and reports. A common method for naming a table is to use Camel Case where all the letters of the table name have no spaces between them and the first letter of a new word is in upper case. The table name 'Phone Number' would become 'PhoneNumber'.

Figure 5. Save... Table Name

Test your knowledge

Designing a table

1. It is a good idea to have a table design on paper before you create the table in the database? TRUE → Absolutely Correct! FALSE → Incorrect, click here to review the topic. 2. When working on the table design it is a good idea to; (a) Have the primary key as the first field(s) of the table. → Almost correct, all these choices are correct. Click here to review the topic. (b) Not use spaces in the field names → Almost correct, all these choices are correct. Click here to review the topic. (c) Add descriptions to the fields to assist in documentation → Almost correct, all these choices are correct. Click here to review the topic. (d) Use mandatory fields to ensure the integrity of your data → Almost correct, all these choices are correct. Click here to review the topic. (e) All of the above → Absolutely correct! 3. It is a good idea to have a spaces in the table name? TRUE → Incorrect, spaces will be problematic. click here to review the topic. FALSE → Absolutely Correct! Your score is 0 / 0