Creating Multiple Base Tables

Within Aptify, an entity can have multiple base tables. This is particularly useful for complex entities that have a large number of fields and for entities that you want to replicate across servers. When using multiple tables, you can spread the entity's fields across the tables so that the potential row size of any single table does not exceed the SQL Server limit of 8060 bytes.

Row Size Limit

The row size limit of 8060 bytes was a hard limit for versions earlier than SQL Server 2005. Attempts to save a record whose size exceeded this limit resulted in an error. With SQL Server 2005 and later, the server can automatically handle situations where the row size exceeds the 8060 bytes without generating the error. However, replication still requires adherence to the 8060-byte limit, so any entity that you intend to replicate must have a maximum row size that is less than the limit. Also, in any case, Aptify recommends designing tables that are less than the 8060-byte limit to ensure optimum performance.

Each time you create a new entity, add a new field, or modify the SQLField Size for an existing field, the system automatically adds up the SQLField Sizes for fields in a particular table to determine if a row in that table could potentially exceed the 8060 bytes limit. If the row size exceeds 8060 bytes, Aptify displays a warning message to notify you that a row in this table could exceed the limit. Note that in this calculation, the field size for unicode character types, such as nchar and nvarchar, is multiplied by 2 since SQL Server uses 2 bytes to encode each unicode character.

Keep in mind that even if you receive the warning message that the table's row size may exceed the 8060-byte limit, a row will only reach this maximum size if a user enters the maximum number of characters for variable length fields. For example, if you have an nvarchar(4000) field in your entity, Aptify will use 8000 bytes for this field when calculating the maximum row size. However, this field will only actually use 8000 bytes if a user enters 4000 characters, which may be unlikely depending on the nature of the field. Also, Aptify supports the nvarchar(max) and varchar(max) data types. In general, you should use one of these data types for fields that can contain large amounts of data. Also, the likelihood of approaching the table size limit is reduced dramatically if you use these max data types in place of large nvarchar or varchar data types. See the About the Supported SQL Data Types for more information on the nvarchar(max) and varchar(max) data types.

Step-By-Step Instructions

If you are creating a complex entity with a large number of fields or if Aptify has warned you that your the row size may exceed the 8060-byte limit, you can add additional base tables to your entity.

Follow these steps to add another table to your entity:

1. Open a new or existing Entities record.
2. Click the plus (+) sign to the right of the Base Table field to open the Additional Base Table dialog.
   
   
3. Click the new icon to open a new Additional Base Tables record.
4. Enter a name for the new table in the Table field.
   • The table's name cannot contain spaces or be a SQL reserved word. See the Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx) for more information on the naming restrictions for tables and other identifiers.
   • Aptify recommends that your table name contain the name of the entity's primary base table and a description of the fields that it will contain, for example, SampleServiceDetailTab.
     
     
5. If applicable, select an alternate filegroup in the File Group field and click OK.
   • Note that the Text/Image File Group field is disabled since no qualifying fields with text or image data types have been added to the table as of yet.
   • See Creating Multiple Filegroups for more information about filegroups.
     
     
     
6. Add additional tables as necessary.
   • Alternatively, you can click OK and New in Step 5 to save the current record and open a new Additional Base Tables record in one step.
     
     
7. Click Close to close the Additional Base Tables dialog and return to the Entities record.
8. Assign one or more fields to the new table. See Defining Entity Fields for details.
9. Save and close the Entities record.

Notes Concerning Multiple Base Tables

When you add additional base tables to an entity, Aptify automatically handles the integration of multiple base tables for a single entity and generates all of the required database objects, such as stored procedures and a single base view. Keep in mind the following points when working with an entity that contains multiple base tables:

• The ID field must reside in the main base table.
• Each record in an entity spans all of the entity's tables. Each table has an ID field. For each record, the ID field is the same value for all of the entity's tables. This links the tables together to form a single record.
• Any field that links back to a parent ID field, such as in a sub-type entity, must reside in the main base table. See Creating Multiple Filegroups for more information on sub-types.
• A computed field and all of the fields that are referenced in its calculation must be in the entity's primary base table. See Calculated and Computed Virtual Fields for details.
• When adding a new field, you can assign it to any of the entity's base tables (keeping in mind the restrictions listed above).
• After you have saved a field in one table, you can move it to another table, if necessary (keeping in mind the restrictions listed above). Follow these steps to change a field's table assignment:

1. 1. Open an Entities record.
   2. Open the Fields record for the field whose table you want to change.
   3. Select a different table from the Base Table drop-down list.
      
      
   4. Click OK to save the Fields record.
   5. Save and close the Entities record. The field and any data contained in that field moves to the specified table.

• After you create one or more additional base tables, the plus sign to the right of the Base Table field on the Entities form changes to a check mark. Click this check mark to open the Additional Base Tables dialog. The dialog reports the other base tables associated with this entity and displays the number of fields that are associated with each table.