Database Tables Can Be Related or Connected to Other Tables by Common Key Fields.

Tables are essential objects in a database because they hold every last the selective information or data. For illustration, a database for a business hindquarters have a Contacts board that stores the names of their suppliers, e-mail addresses, and telephone numbers pool. Because another database objects depend then heavily on tables, you should always set forth your design of a database by creating all of its tables and then creating any strange objects. Before you create tables, consider your requirements and determine completely the tables that you might need. For an unveiling to planning and artful a database, see Database excogitation basics.

In this article

• Overview

• Table and field properties

• Data Types

• Postpone relationships

• Keys

• Benefits of using relationships

Overview

A relational database the likes of Access commonly has some incidental tables. In a fortunate-designed database, all shelve stores data about a particular subject, such as employees or products. A table has records (rows) and fields (columns). Fields experience different types of data, much as text, numbers, dates, and hyperlinks.

1. A record: Contains specific data, like information about a particular employee or a product.

2. A field: Contains information about one aspect of the prorogue content, such atomic number 3 world-class name or e-ring armor cover.

3. A field value: Each phonograph recording has a field value. For instance, Contoso, Ltd. OR someone@exemplar.com.

Top of Page

Table and battlefield properties

Tables and fields also have properties that you can set to control their characteristics or behavior.

1. Remit properties

2. Field properties

In an Access database, table properties are attributes of a table that affect the appearance or behavior of the table as a whole. Prorogue properties are ordered in the table's property sheet, in Design position. E.g., you arse set a table's Default Though property to delimitate how the table is displayed by default.

A field property applies to a finical battlefield in a table and defines one of the field's characteristics operating room an aspect of the field's behavior. You can situated roughly field properties in Datasheet view. You stool also set some field property in Design view by using the Field Properties panelling.

Data types

All field has a data type. A field's information case indicates the good-hearted of data that the subject area stores, such as wide-ranging amounts of text OR bespoken files.

A data typewrite is a field property, just IT differs from other field properties as follows:

• You set a field's data type in the table design grid, non in the Field Properties pane.

• A champaign's data case determines what else properties the field has.

• You must set a field's data type when you create the force field.

  You can make a new field in Access by entering data in a new column in Datasheet view. When you create a field by entering data in Datasheet view, Access mechanically assigns a information case for the field, supported on the value that you enter. If no different data type is implied by your input, Access sets the data case to Text. If needed, you can variety the data type by using the Thread.

Examples of automatic data type detection

The following table shows how automatic data type detection works in Datasheet view.

If you enter:	Access creates a field with a data type of:
John	Text
http://www.contoso.com You can use whatsoever valid Internet protocol prefix. For example, http://, https://, and mailto: are valid prefixes.	Hyperlink
1	Number, Long Integer
50,000	Number, Long Whole number
50,000.99	Number, Double
50000.389	Number, Double
12/67 The date and time formats recognized are those of your substance abuser venue.	Date/Meter
December 31, 2016	Date/Time
10:50:23	Date/Time
10:50 am	Date/Time
17:50	Date/Time
$12.50 The currency symbol recognized is that of your user locale.	Currency
21.75	Come, Double
123.00%	Number, Double
3.46E+03	Number, Stunt woman

Top of Page

Table relationships

Although each remit stores data about a different subject, tables in an Get at database unremarkably store data near subjects that are incidental each other. For example, a database might arrest:

• A customers table that lists your company's customers and their addresses.

• A products table that lists the products that you betray, including prices and pictures for for each one item.

• An orders table that tracks client orders.

Because you store information about contrastive subjects in separate tables, you need some way of life to tie up the data in concert so that you can easily immix related data from those separate tables. To connect the information stored in different tables, you create relationships. A family relationship is a logical connection between two tables that specifies Fields that the tables have in common. For more information, see Guide to table relationships.

Top of Page

Keys

Fields that are part of a table relationship are called keys. A Francis Scott Key usually consists of one field, merely may consist of more than one bailiwick. There are two kinds of keys:

• Primary key    A table can have entirely one direct key. A primary key consists of one or more fields that uniquely discover all disk that you store in the defer. Much, there is a single identification number, such as an ID number, a serial number, or a code, that serves as a primary key. For instance, you might have a Customers board where each customer has a unique customer ID total. The customer ID field is the primary Florida key of the Customers table. When a primary quill key contains more than one field, IT is usually composed of pre-existing fields that, taken put together, cater alone values. For example, you might use a combining of last name, first name, and birth particular date equally the primary key for a table about people. For more information, see adding or changing a table's important key.

• Foreign Francis Scott Key    A table can also cause one Beaver State more foreign keys. A foreign key contains values that gibe to values in the primary key of another table. E.g., you might bear an Orders table in which from each one order has a customer ID number that corresponds to a record in a Customers table. The client ID field is a international key of the Orders table.

The correspondence of values 'tween paint fields forms the basis of a table relationship. You habituate a table relationship to combine information from related tables. For example, suppose that you have a Customers table and an Orders shelve. In your Customers table, each record is identified by the primary key field, ID.

To associate each order with a customer, you add a nonnative key field to the Orders table that corresponds to the ID field of the Customers defer, and then create a relationship between the two keys. When you bring a record to the Orders put over, you use a value for customer ID that comes from the Customers table. Whenever you want to view some information roughly an order's customer, you use the kinship to identify which information from the Customers remit corresponds to which records in the Orders table.

1. A primary election key, identified by the headstone icon next to the theater name.

2. A foreign key — note the petit mal epilepsy of the cay icon.

Do not bestow a bailiwick if you expect that from each one unique entity represented in the table might require more than economic value for the field. Continued the pre-existent example, if you want to start tracking orders placed by your customers, you practice not minimal brain damage a field to the table, because each customer volition have Sir Thomas More than one order. Or else, you make up a new defer to lay in orders, and then create a relationship between the two tables.

Pinch of Pageboy

Benefits of using relationships

Keeping information separated in related tables produces the following benefits:

• Consistency    Because all item of data is recorded solitary once, in one set back, in that respect is little opportunity for ambiguity or inconsistency. E.g., you store a client's name only once, in a table about customers, rather than storing IT repeatedly (and possibly inconsistently) in a set back that contains society information.

• Efficiency    Recording data in merely peerless place means you use less disk blank space. Moreover, smaller tables tend to provide data more quickly than big tables. Last, if you don't utilisation separate tables for fall apart subjects, you will put in null values (the absence of data) and redundancy into your tables, both of which can waste blank and hinder performance.

• Understandability    The design of a database is easier to understand if the subjects are in good order separated into tables.

Plan your tables with relationships in mind. You can expend the Lookup Wizard to create a foreign key sphere if the table that contains the corresponding primary key already exists. The Lookup Wizard creates the family relationship for you. For more information, see Create or erase a lookup field.

Top of Page

Database Tables Can Be Related or Connected to Other Tables by Common Key Fields.

Source: https://support.microsoft.com/en-us/office/introduction-to-tables-78ff21ea-2f76-4fb0-8af6-c318d1ee0ea7

Share this post