CCNC/CCNC Module 5/Designing and creating tables/Table Design and Layout

From WikiEducator
Jump to: navigation, search
Tutorial.png Table Design and Layout 

Field Design and Properties | Adding Columns | Adding Indexes | Database Schemas | Self Assessment | Summary & FAQs

Field Design and Modification

Icon objectives.jpg


Upon completion of this tutorial the learner will be able to:
  • Identify different data types
  • Modify the design of a database table
  • Discuss the impacts of modifying and extending a database design
  • Describe the challenges associated with moving a table column
  • Widen a column while viewing data

Exploring the data types

Figure 1. Data Types

It's a good idea to familiarize yourself with all the different data types available when adding fields to a table. The table displayed here provides an implementation of many of the data types. Once you have added the different fields, click on each one and view the fields properties. Feel free to play around and get a sense of the differences and similarities among the different data types.

Modifying a fields properties

Figure 2. Field Properties

When you create the fields you will notice the field properties section at the bottom of the screen that allows you to change the field properties such as the size of the field, the number and date format, etc. To change these attributes you will need to click on the field at the top which will give you access to it's properties displayed in the bottom section of the screen.

To change the field size click on the field to select it. A green arrow will appear to indicate that it has been selected. Then click in the white box next to Length. Type in the number of characters you want the field to be restricted to.

If you want to change the number or date format click on the grey button next to Format example. This will bring up a dialog box which will allow you to choose a format for the number or date.

Consequences of change

It is important to note that if you change the field size in a table and there is already data in the table you may lose some of that data. For example, if you change a field size from 50 characters long to 10 characters long and there is a record that already contains 20 characters in that particular field you will lose (or truncate) the data; so be very careful.

Moving the columns within a table

Being able to re-order the fields or columns within a table is a useful feature. This feature is only available when you are first designing the table. Once the table has been saved the field order is also dependent on the data stored in the table. Changing the order of the fields would also mean the database would have to re-order the data. The data re-organization would have dependencies on the tables rules, therefore making it a complicated process. This re-ordering should be done with careful consideration and with the assistance of a database administrator so no data is lost.

Widening the columns

Sometimes the columns containing the fields are not wide enough to display the data fully. You can widen the columns by opening the table. Click on the table, right click and select Open from the pop-up menu. Move your mouse up between the field names in the grey area (as you would to widen columns in a spreadsheet), double click on the vertical line separator. Another method to change the columns width is to place the mouse between the field names, hold down the left mouse button and drag the column width to be wider or narrower.

Test your knowledge

Icon qmark.gif

Table design and layout


1. Making a field longer is different than making in wider?

TRUE. they are different.
Absolutely Correct!
FALSE, longer and wider is the same in a database field.
Incorrect; longer means the field has more characters, wider means more characters are displayed. click here to review the topic..

2. A LONGVARBINARY data type can be used as the field to store an image?

Absolutely Correct!
Incorrect, the image data type is a LONGVARBINARY. click here to review the topic..

3. Which data type would be best to store a single character?

Incorrect, click here to review the topic.
(b) CHAR
Absolutely correct!
Incorrect, click here to review the topic.
Incorrect, click here to review the topic.
Incorrect, click here to review the topic.

Your score is 0 / 0