Creating relationship rules—Help | ArcGIS for Desktop
Table manners are the rules used while eating, which may also include the use of utensils. When eating soup, the spoon is held in the right hand and the bowl tipped . "Miss Manners: Reading at the Breakfast Table – MSN Relationships. Learn the proper table setting etiquette for how to set a table for a casual or Now that you know the basic table setting rules, brush up on your table etiquette. That's where the other data normalization rules come in. A customers In a properly designed database, new data adds rows, not fields. (When THE FIX: Setting up correct table relationships is the second half of good database design.
Fixing Access Annoyances by Phil Mitchell, Evan Callahan
An example is a product description. This is a sure sign that this information belongs in a separate table Figure This orders table is not yet fully normalized. Note that product information is repeated verbatim in records 2 and 3. This reusable information should be stored in its own table.
Normalized orders and products tables. The product descriptions have been replaced with product codes that refer back to the products table. For instance, if you create a separate table for orders, you need a way to track which orders go with which customers. MSKB is a good, non-technical article on normalization. For a slightly more technical tutorial, check out http: More to the point, what is a key, and what is a primary key, and why should I care? Setting up correct table relationships is the second half of good database design.
Defining relationships between tables is how you pull that related data back together again. Field Names and Data Types Once you have designed your tables, creating them in Access is pretty straightforward. Field names must be unique within a table but can be reused in other tables. The trickier part is assigning a data type to each field. Unlike with a table in a Word document, for example, with an Access table you must specify what kind of data you intend to put in each field.
Because a database knows what kinds of values are in a specific type of field, it can sift, collate, sort, and view different slices of data in myriad ways and can prevent some kinds of data from interacting in certain undesirable ways. Here are some simple rules to follow when choosing data types: For money, use the Currency data type.
Here are some very basic guidelines. If your numbers are integers i. So make it a Text field. For foreign keys, you must use the data type of the primary key that the foreign key refers to. For example, if the primary key is an AutoNumber, use the Number data type with the Field Size set to Long Integer for the foreign key. And duplicate records cause all sorts of headaches. But you can run into different problems with using meaningful fields.
And even with a meaningful key, you can still enter duplicate records if, for example, you use a slightly different spelling of the name. For these reasons, we recommend using an AutoNumber ID field in most cases. Good table design requires that every table have at least one field that acts as a unique key. We call this the primary key field. After all, you could have two customers with the same business name in different states.
The numbers are meaningless—they simply serve as unique identifiers. To see how keys are used to create relationships, consider two tables: So how do you create a report that tracks which order goes with which customer, and presents all that information on one screen? If no table relationships have been defined and you are opening the Relationships window for the first time, Access prompts you to add a table or query to the window. Open the Relationships window Click File, and then click Open.
Select and open the database. On the Database Tools tab, in the Relationships group, click Relationships. If the database contains relationships, the Relationships window appears. If the database does not contain any relationships and you are opening the Relationships window for the first time, the Show Table dialog box appears.
Click Close to close the dialog box. On the Design tab, in the Relationships group, click All Relationships. This displays all of the defined relationships in your database. Note that hidden tables tables for which the Hidden check box in the table's Properties dialog box is selected and their relationships will not be shown unless the Show Hidden Objects check box is selected in the Navigation Options dialog box.
A table relationship is represented by a relationship line drawn between tables in the Relationships window. A relationship that does not enforce referential integrity appears as a thin line between the common fields supporting the relationship. When you select the relationship by clicking its line, the line thickens to indicate it is selected. If you enforce referential integrity for this relationship, the line appears thicker at each end.
When the Relationships window is active, you can select from the following commands on the ribbon: On the Design tab, in the Tools group: When you select a relationship line, you can click Edit Relationships to change the table relationship.
You can also double-click the relationship line. The report shows only the tables and relationships that are not hidden in the Relationships window. On the Design tab, in the Relationships group: Note that hidden tables tables for which the Hidden check box in the table's Properties dialog box is selected and their relationships will not be shown unless Show Hidden Objects is selected in the Navigation Options dialog box.
If you made any changes to the layout of the Relationships window, you are asked whether to save those changes. Top of Page Create a table relationship You can create a table relationship by using the Relationships window, or by dragging a field onto a datasheet from the Field List pane. When you create a relationship between tables, the common fields are not required to have the same names, although it is often the case that they do.
Rather, those fields must have the same data type.
Create, edit or delete a relationship - Access
If the primary key field is an AutoNumber field, however, the foreign key field can be a Number field if the FieldSize property of both fields is the same. When both common fields are Number fields, they must have the same FieldSize property setting. Create a table relationship by using the Relationships window Click File, and then click Open.
If you have not yet defined any relationships, the Show Table dialog box automatically appears.
If it does not appear, on the Design tab, in the Relationships group, click Show Table. The Show Table dialog box displays all of the tables and queries in the database. To see only tables, click Tables. To see only queries, click Queries. To see both tables and queries, click Both. Select one or more tables or queries and then click Add. When you have finished adding tables and queries to the Relationships window, click Close.
Drag a field typically the primary key from one table to the common field the foreign key in the other table. To drag multiple fields, press the CTRL key, click each field, and then drag them. The Edit Relationships dialog box appears.
Verify that the field names shown are the common fields for the relationship. If a field name is incorrect, click the field name and select a new field from the list. To enforce referential integrity for this relationship, select the Enforce Referential Integrity check box.
For more information about referential integrity, see the Understanding Referential Integrity and the Enforce Referential Integrity sections. The relationship line is drawn between the two tables.
If you selected the Enforce Referential Integrity check box, the line appears thicker at each end. This means the Indexed property for these fields should be set to Yes No Duplicates.
If both fields have a unique index, Access creates a one-to-one relationship.How to Set a Table Properly - Good Manners
This means the Indexed property for this field should be set to Yes No Duplicates. The field on the "many" side should not have a unique index. When one field has a unique index and the other does not, Access creates a one-to-many relationship.
Create a table relationship by using the Field List pane You can add a field to an existing table that is open in Datasheet view by dragging it from the Field List pane.
The Field List pane shows fields available in related tables and also fields available in other tables. When you drag a field from an "other" unrelated table and then complete the Lookup Wizard, a new one-to-many relationship is automatically created between the table in the Field List pane and the table to which you dragged the field.
This relationship, created by Access, does not enforce referential integrity by default. To enforce referential integrity, you must edit the relationship. See the section Change a table relationship for more information.
Open a table in Datasheet view On the File tab, click Open. In a composite relationship, destination objects can't exist independently of origin objects, so when the origin is deleted, the related destination objects are also deleted in a process called a cascade delete.
This dependency rule is also enforced by the ArcMap Validate Features command, a command you run in an edit session to test referential integrity. If you created a destination object but didn't associate it with an origin object, Validate Features warns you of the error. A composite relationship can also help you maintain features spatially; moving or rotating an origin feature causes the related destination features to move or rotate with it when messaging is set to Forward.
Composite relationships are always one-to-many when you create them but can be constrained to be one-to-one with relationship rules. Origin and destination classes When you create a relationship class, you choose one class to be the origin and another to be the destination. It is important not to confuse the two.
With the behavior of cascade deletes in composite relationships, the importance of this may seem obvious. In simple relationships, getting this correct is critical. This is because when you delete a record in the origin class, the simple relationship class finds the matching records in the destination class and sets the value of their key fields to Null.
If you choose the wrong class as the origin and delete objects in the origin, you will introduce errors into the foreign key field. The following example illustrates how this can occur: Parcel to Zone wrong This is a common scenario for error. The Zone table contains the descriptions for the different zoning codes and is conceptually similar to an ArcGIS for Desktop Advanced Workstation lookup table.
In this case, the Parcel class is the origin, and the Zone table is the destination. The problem is that when you delete a parcel, the value in the key field Zone is set to Null for the matching record in the Zone table, and none of the other parcels that have that zoning code have a match in the Zone table.
Zone to Parcel correct To correct the problem, set the Zone table as the origin. Deleting a parcel a destination object will have no effect on the Zone table, and deleting a Zone code an origin object will simply set the value of the Zone field in the matching parcel records to Null, which is as it should be, because they no longer have a matching Zone table record.
Primary and foreign keys In a relationship class, objects in the origin match objects in the destination through the values in their key fields. In the following example, parcel matches permits 2 and 3 because all those records have the same parcel ID.
The key field in the origin class of a relationship is called the primary key and is often abbreviated as PK. Unlike a true primary key, the values in the primary key field in a relationship are not required to be unique for every object.
- Creating relationship rules
- How to Set a Table: Basic, Casual, and Formal Table Settings
- Create, edit or delete a relationship
The key field in the destination class is called the foreign key and is often abbreviated as FK. It contains values that match those of the primary key field in the origin class.
Again, the key field values do not need to be unique for each row. The key fields may have different names but must be of the same data type and contain the same kind of information, such as parcel IDs. Fields of all data types, except binary large object BLOBdate, and raster, may be key fields. You specify the key fields when you create a relationship class. When deciding on a primary key field, one option is to use the row ID field, commonly referred to as the ObjectID field. This field guarantees a unique ID for each record.
It is maintained by ArcGIS, and you can't modify it. The ObjectID value of a given object never changes as long as it remains in its original class and, if the object is a feature, you do not split it. If you split a feature, it will maintain the original feature but will update the geometry and create a new feature, which will have a new ObjectID assigned to it.
As a result, only the feature with the original ObjectID will maintain any relationships that are dependent on the ObjectID value. Because of this, it may be better to create and use your own primary key field instead of relying on the ObjectID field. The following describes how your own primary key field can help maintain relationships when you perform each of these operations. When you import records to another feature class or table, new ObjectID values are assigned, losing any relationships based on the original ObjectID values.
If, instead, you base the relationship on another primary key, the ID values in the primary key will not change when records import.
This allows you to preserve relations when you import related sets of objects to new classes.
Replicating with relationship classes where an ObjectID field is used as a primary key field requires additional processing during synchronization, which can affect performance.