Tables

See Also      Tasks

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.

  

  For information about table owners, see Working in a Multi-User Environment.

  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. For information about saving modified tables and diagrams, see Controlling Database 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. For information about primary keys, see Defining a Primary Key.
  
  
• Property Columns   The set of property columns is visible in the Column Properties view or Customized view 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.

  

  For more information about column properties, see Column Properties. For more information about table views, see Changing a Table View.

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. For information about data types, allowing null values, and other column properties, see Columns.
  
  
• Whether to use constraints and if so, where. For more information, see Constraints.
  
  
• What types of indexes you need, where you need them, and which columns should be the primary key and foreign key. For more information, see Indexes.

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

The Database Designer has two modes for working on tables. You can view tables directly in a database diagram using Column Properties view or you can open a single table in design mode by right-clicking the table name and choosing Design. This opens the table in a single window where you can define columns and properties for the table.

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’ll 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 enables you to try out different designs by working with tables in your 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, any data that is stored in the table is preserved to the extent possible. When you’re satisfied with your basic design, you can add constraints, indexes, and any additional columns that you require. For more information, see Working in Database Diagrams.