CCNC/CCNC Module 5/Designing and creating tables/Basic Table Operations/Design A Table
|Basic Table Operations|
Design a Table
Adding Fields to your 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.
- 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'.
- the words table and entity are interchangeable
- the words field and attribute are interchangeable
Adding 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
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
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.
- 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'.
Test your knowledge