Defining fields in tables

Fields are the components that provide structure for a table. In fact, you can't have a table without fields. For instance, you can create an empty table that has fields defined but no rows (records).

In databases, fields are also used to maintain relationships between tables. This is done by having matching fields in two or more tables. For example, if you stored a table called toy_store in a database and you also stored a staff table to track the employees in each store, you would create a common field between the two tables that would be populated with, for instance, a store ID. The store ID value for a specific toy store would be the same in both tables.

Below, a store ID field has been added to the toy_store table:

Toy store table with store ID

The toy_store table is linked to an employee table by the store ID. The table below shows three employees of the Play House:

Employee table linked to store table with ID

Certain fields are also used to maintain relationships between tables and their attribute indexes.

Fields in a table store the same category of data in the same data type. For example, if you have a NAME field in a table of customers, the entries for this field are all customer names and are stored as text. You wouldn't mix the entries—you wouldn't put a customer name in this field for one record and a product name in this same field for another record.

When you create a table or add fields to an existing table, you define what data type is used to store the data in each field. In some cases, you also specify the length of the field.

Field names

Field names are the names you give to the columns in a table. The names should give an indication of what data is contained in that particular column. For example, when you create a new feature class in ArcCatalog, the table is already populated with an ObjectID field and a shape field. The ObjectID field contains the unique ID number for each object in the feature class. The shape field defines what type of shape is stored in the feature class: point, line, polygon, multipoint, or multipatch.

You might also consider using set phrases to indicate the type of column. For example, if you create a separate unique ID on a table that you will use for indexing purposes, you might name the field ID_UK—the UK indicating this is a unique key.

Field names in the same table must be unique; for instance, you can't have two columns with the name ObjectID. Field names must also start with a letter and not contain spaces or reserved words. Field names are limited to 64 characters for both file and personal geodatabases, 31 characters in SQL Server and SQLExpress, 30 characters in Oracle and DB2; dBASE is limited to 10 characters.

Certain field names appear in ArcGIS with their fully qualified names for tables stored within an ArcSDE geodatabase. For example, if you create or import a polygon feature class that contains a field named Area, the database, schema, and table name are appended to it. This is the name you see in the attribute table of the feature class. That means for a polygon feature class named archsites, stored in the prof schema of the museum database, the Area field would look like this:

MUSEUM.PROF.ARCHSITES.AREA

The following list contains all the field names that are fully qualified within an ArcSDE geodatabase:

FID, AREA, LEN, POINTS, NUMOFPTS, ENTITY, EMINX, EMINY, EMAXX, EMAXY, EMINZ, EMAXZ, MIN_MEASURE, MAX_MEASURE

For cases such as this, you might consider using a different field name or a field alias.

Field aliases

Field aliases allow you to assign an alternate name for a field. You normally use field names that are as short as possible to convey what data is stored in that field. You also cannot use spaces or special characters in the field name, and as shown above, certain fields show up in the table with their fully qualified names. In these cases, you could use a field alias to give the field a more descriptive name. For example, if you had a field named ST_SUFX that stored the type of street, which was indicated by the suffix used on the street name, you could give this field an alias of Street name suffix.

Learn how to set a field alias

Tip:

Geoprocessing methods exist to let you validate table and field names. See Working with geodatabases in Python for more information.

Using domains to control field values

Attribute domains are rules that indicate valid values for a field in a table in a geodatabase. They enforce data integrity by restricting what data values a user can add to a particular field.

You would apply attribute domains to fields only if there was a definable set or range of specific values possible for that field. For example, a field that stores the answer to the survey question What is your favorite food? would be difficult to apply a domain to, since there are a large number of responses that can be given. However, a field storing data on eye color could have an attribute domain assigned to it, because there are only a few possible valid values.

• Black
• Brown
• Blue
• Green
• Hazel
• Gray
• Violet

Using an attribute domain for a field storing eye color data would ensure consistency of the values. If data collectors were allowed to type any color into a text field for eye color, you might end up with any of the following for blue eyes:

• Azure
• Navy
• Sky blue
• Cobalt
• Aquamarine

Attribute domains also prevent misspellings or typographical errors. Even if data collectors knew only to use the term blue for blue eyes, they might misspell the word (bleu) or mistakenly strike the wrong key when typing blue (vlue) into a text field.

Types of attribute domains

There are two types of attribute domains you can use to restrict field values: coded value domain and range domain.

Coded value domain—Uses codes to define a set of allowed values for a field that stores discrete data

You can use a coded value domain for any data type. For the eye color field, you could create a coded domain. These are examples of two possible code sets:

• Blk = Black
• Brn = Brown
• Blu = Blue
• Grn = Green
• Hzl = Hazel
• Gra = Gray
• Vlt = Violet

Or

• 1 = Black
• 2 = Brown
• 3 = Blue
• 4 = Green
• 5 = Hazel
• 6 = Gray
• 7 = Violet

Range domain—Defines a range of allowed numeric values for a field

The field must be a numeric or date data type to use a range domain. An example of a field to which you might apply a range domain is one that stores data on birth weights for single live births of western lowland gorillas in zoos. The range would run from the lowest weight (1 kg) to the highest (2.5 kg).

For additional information on attribute domains, see A quick tour of attribute domains.

To learn how to create an attribute domain, see Creating a new attribute range domain and Creating a new attribute coded value domain.

Using subtypes

Subtypes are classifications within a feature class or table in a geodatabase. They allow you to logically group features based on a unique characteristic or behavior of the data. This characteristic or behavior is represented by the values of one field in the table. For example, for a table of hydrology, you could have subtypes for different types of waterways, such as creeks, streams, channels, canals, and rivers. For each of these subtypes, you could apply different topology rules, connectivity rules, default values, and relationship rules.

Using subtypes to store groups of related features can improve query performance. If you stored the different types of data in separate feature classes instead of utilizing subtypes, you would have a greater number of feature classes in the database, and it could take longer to search.

The following are rules about subtypes:

1. Only one field in a table or feature class can have subtypes applied to it.
2. To use subtypes, the field on which you base the subtype must be a long or short integer field.
3. You can apply different topology and relationship rules to different subtypes. You can also apply different attributes or coded domains to other fields in the table based on subtypes.

Steps to apply subtypes

Steps:

1. Make sure the field to which you want to apply the subtype is a short or long integer field. If it is not, add a short or long integer field to the table or feature class. In most cases, short integer should suffice. However, if there is any possibility your subtype values could exceed 32,767, use a long integer field.

   For example, for a feature class of rivers, you could add a short integer field called Watershed to create subtypes based on the watershed to which the river contributes.

2. On the Subtypes tab of the Properties dialog box for the table or feature class, specify the subtype field by choosing it from the first drop-down list.

   For the rivers example, you would choose the Watershed field from the Subtype Field list.

3. A new subtype is automatically added to the Subtypes table. This default subtype has a code of 0 and a description of New Subtype. You can double-click in each of these fields to type the subtype code and description that you want.

   You could change the first code to 1 and the description to the name of the first watershed.

4. To add more subtypes, continue adding subtype codes and descriptions to the Subtypes table.

   In the field below code 1, you could add a code 2 with a corresponding watershed name in the Description field, then below that, add a code 3 with a corresponding watershed name, and so on, until you have created codes and descriptions for all the watersheds represented in your rivers feature class.

5. To specify different default values or domains per subtype, click a subtype in the Subtypes list. In the Default Values and Domains list, you can type a default value for any of the fields in the list. You can also apply a coded or attribute domain to fields in the list by clicking in the Domain field and choosing a domain from the drop-down list. If no domains exist yet, you can create a new one by clicking the Domains button at the bottom of the Properties dialog box, which takes you to the Workspace Domains dialog box.

   The default values and domains you specify apply only to the subtype you chose from the Subtypes list. If you click another subtype in the Subtypes list, the default values and domains will either be empty (if you haven't specified default values and domains for this subtype) or will contain different values.

Learn more about subtypes