Integer to GUID conversion notes for Inspector Blair project.

The grouping feature offline caching requires GUID values in place of integer ID values. I had preferred to avoid this conversion for the following reasons:

• The work is significant, yet it produces no visible progress for internal customers.
• GUID values are much larger than integer values, especially when rendered into HTML. The extra database and network load should be negligible in our environment, but it could have some impact on mobile devices.
• GUIDs carry a significant penalty for index clustering and relationships.

<option value = "BC072ADE-ACBF-42B0-8FCA-5587E0FE95BC">Inspector – Biosafety</option>
<option value="1">Inspector – Biosafety</option>

Ultimately, I elected to migrate to GUID due to Inspector Blair’s unique workflow. Records may be created independently by multiple users, sometimes offline, and later synchronized. Although GUIDs add storage, payload, and indexing overhead compared with integers, the project’s relatively low data volume makes that tradeoff acceptable and avoids a more complex dual-identifier synchronization model.

Switching to GUID values shouldn’t cause a real-world noticeable performance issue. I had still hoped to keep integer IDs for maximum speed, but GUIDs are required in order to support grouping and integrate that grouping with lists of individuals.

The account records were converted using the process below. The same general process may also be applied to inspections, system logs, autoclaves, and buildings.

Note to any readers – this conversion process is on a development environment with dummy data and so I could get away with a lot of shortcuts. Real-world migration necessitates at minimum backups, transactions, redundant validations, retention of previous keys, and a robust restoration plan.

Table Conversion

For the primary table:

• Rename the existing integer ID field to ID_OLD.
• Create a new uniqueidentifier field named ID.
• Set RowGuid to True.
• Set the default binding to NEWSEQUENTIALID().

For tables that are not part of a relationship:

• Set the new ID field as the primary key.

Relationship Conversion

For tables that participate in parent-child relationships:

• Rename the integer foreign key field in the child table from FK_ID to FK_ID_OLD.
• Create a new uniqueidentifier field in the child table named FK_ID.

Populate the new child foreign key from the parent GUID:

UPDATE child
SET child.FK_ID = parent.ID
FROM child
INNER JOIN parent
    ON child.FK_ID_OLD = parent.ID_OLD;

Run a simple verify.

SELECT COUNT(*) AS MissingParentReferences
FROM child
WHERE FK_ID IS NULL
  AND FK_ID_OLD IS NOT NULL;

Then update the relationship structure:

• Delete the old ID_OLD to FK_ID_OLD relationship between the parent and child tables.
• Set the new ID field in the parent table as the primary key.
• Set the new FK_ID field in the child table as the foreign key.
• Create the new one-to-many relationship:
  • Primary key table: ID
  • Foreign key table: FK_ID
  • Insert and update update rule: Cascade

Stored Procedure and XML Updates

Update all stored procedures and XML handling that reference the affected IDs:

• Retype all @id variables in stored procedures from int to uniqueidentifier.
• Retype all int values in XML variables to uniqueidentifier.
• For selected-item list tables, such as account roles, convert the relevant list fields to GUID as well.
• No detailed notes are available for selected-item list tables, but the same parent-child update pattern should apply.

Detail Display Page Updates

For detail display pages, as opposed to data list pages, make the following stored procedure updates.

Use the dbo.account stored procedure as the example.

• Replace the primary table variable, such as @tempMain, with a temp table, such as #primary_cache.
• This does not change functionality, but should improve performance.
• Navigation queries can no longer use output variables.
• Output all relevant navigation data as the first record set instead.
• When no ID is provided, run a SELECT TOP 1 query to pre-populate the detail view.

PHP Display Page Updates

Use account.php as the example.

• Remove all navigation variables from the stored procedure call.
• Remove the associated PHP navigation variables.
• Read the first record set and place the returned values directly into the navigation object.
• Immediately after reading the navigation record set, add:

$query->get_next_result();

• Use the next result set for the main data record.
• If the detail view contains sub-data, remove array verification from the sub-table update code.
• Add array verification to the xml() method in the relevant data class file.
• Add the script URL reference for the dc_guid function.
• In all insert functions, create a single string variable populated by dc_guid. Replace all $temp_int references with the new GUID variable. Remove global $temp_int.

Testing Checklist

Test the updated detail page with the following actions:

• Create a new record.
• Update the record.
• Create a sub-record.
• Update the sub-record.
• Delete the sub-record.
• Delete the main record.
• Test navigation.

List Page Verification

Verify that list pages still point to the correct detail page entries after the conversion.

Delete Flags

Post migration and verify, flush all records with delete flags marked.

Final Steps

This conversion is the first stage of a two-stage GUID migration. During the initial conversion, GUIDs are generated by the database using NEWSEQUENTIALID() so the schema, relationships, stored procedures, XML handling, and PHP pages can be migrated safely from integer IDs to GUIDs. After the conversion is stable, the second stage will shift new record creation toward client-side GUID generation. This will allow offline-created records to receive permanent identifiers before synchronization. Database-generated GUIDs will remain available as standby functionality for server-created records, fallback workflows, and compatibility during the transition.