About Tables

Overview of tables

A database consists of one or more tables. A table is a collection of data, arranged in rows and columns. For example, you might have a table for author information called authors. Each column would contain a certain type of information, such as the authorÆs last name. Each row would contain all the information about a specific author: first name, last name, address, and so on.

In a database, you might have a number of tables, each devoted to a specific topic. For example, the pubs database might contain tables for authors, titles, and so on. Using a separate table for each topic can eliminate duplicate data, make data storage more efficient, and reduce data-entry errors.

Tables are the basic building blocks of database diagrams. In a database diagram, each table is laid out in a matrix so that you can see all the properties defined for every column in your database table.

Tables in a database diagram

Each table in a database diagram has three distinct features: a title bar, a row selector, and a set of property columns.

• Title Bar   The title bar shows the name of the table. If another user owns the table, then that userÆs name appears in parentheses at the end of the table name.

  

  If you have modified a table and have not yet saved it, an asterisk (*) appears at the end of the table name to indicate unsaved changes.

  or

• Row Selector   You can click the row selector to select a database column in the table. The row selector displays a key symbol if the column is in the tableÆs primary key. 
• Property Columns   The set of property columns is visible only in certain views of your table. You can view a table in any of five different views to help you manage the size and layout of your diagram.

  

  Before you begin defining the columns for a table, determine what type of data the table will hold and how that table relates to the other tables in your database.

Designing tables

To determine the structure of a new table, you need to decide:

• What type of data the table will contain.
• What columns you need in the table and the data type (and length, if required) for each column.
• Which columns should accept null values.
• Whether to use constraints and if so, where.
• What types of indexes you need, where you need them, and which columns should be the primary key and foreign key.

After you decide on the structure of your table, you can create the table and define its columns in your database diagram or with the Table Designer. You can also alter the tableÆs appearance in your database diagram so that the information you need is visible when you need it. When you save your table or the database diagram, the table is created in your database.

If you know exactly what you want in a table, it is often most efficient to define everything you need at the beginning, including the tableÆs data restrictions and additional properties. However, in many cases, you will do best to first create a basic table and save it so it is created in your database. You can then add some test data to the table and experiment with the table in the database diagram to fine-tune its design.

The Database Designer lets you try out different designs by working with tables in your database diagram. Through experimentation, you can determine what types of data are frequently entered and queried and then redesign your table accordingly.

When you change a tableÆs design in a database diagram or in Table Designer, any data that is stored in the table is preserved to the extent possible. When you are satisfied with your basic design, you can add constraints, indexes, and any additional columns that you require.