Change 'Child' record when changing 'Parent' record?

[MariaAux]

Hope you're all having a great day. I have a Table of families and a related Table of children, related by ID. On occasions, we need to change the ID of the whole family as they move from 'inactive' to 'Active'. Obviously chnaging the ID of the family leaves the children wondering on their own unrelated to that family and I then ned to go in, search for the children and chnage their ID's as well to get them back with their surname and parents.

How can I automate this that I just change the family ID and both the Children's ID's chnage at the same time?

Thanks so much - I really appreciate it.

[comment]

On occasions, we need to change the ID of the whole family as they move from 'inactive' to 'Active'.

Why would changing the status of a family necessitate changing its ID?

---

Hint: this is a rhetorical question; it shouldn't. An ID should be meaningless and not depend on any attribute of the object.

[MariaAux]

Yes, totally right. Changing the status doesn't affect the ID at all.

This is a database of children registered at an academy. Each family as a unique ID starting from 001, 002 etc, but we often get people interested or who come for trial lessons. We register their family information under the ID of 999, 998 etc. Soem join and others don't, so when they decide to join, we want that family to slot in in the next unique ID. Thus, we would like to change the family ID whilst chnaging the Student ID automatically.

Any easy way to do it?

Thanks so much and have a great weekend.

[comment]

The easy way to do it is not to. The way you have it now, your ID is not meaningless.

[jbante]

Yes, totally right. Changing the status doesn't affect the ID at all.

This is a database of children registered at an academy. Each family as a unique ID starting from 001, 002 etc, but we often get people interested or who come for trial lessons. We register their family information under the ID of 999, 998 etc. Soem join and others don't, so when they decide to join, we want that family to slot in in the next unique ID.

So changing a family's status doesn't change the ID, but changing a family's status from not-joined to joined changes the ID? Don't do that. For one thing, changing IDs for foreign keys in related tables is cumbersome to maintain. For another, you'll eventually get ID collision when the IDs for families that have joined catch up to families that have not joined. If it's important to your organization that IDs only be sequentially assigned to families that have joined, here's how I would approach it:

1. Push back against the need to assign sequential IDs to only families that have joined. If that doesn't work...
   
2. Use two separate ID fields for families, one that the database uses to relate family records to records in other tables (children) "under the hood" (this should not be available in the user interface), and another that users will see as the visible ID in the interface that's only set by script when a family joins. The "under the hood" ID is the actual primary key for the table, and it should never be changed. The user-visible ID should only be set when a family joins, and should never change again after that.
   
3. Create a new table that only has an ID field set to auto-enter a sequential serial number. This is where you'll make family IDs.
   
4. In the script you use to update a family from "not-joined" to "joined," include a section or subscript that goes to a layout based on the new table, creates a new record, saves the ID to a $variable, deletes the record, returns to the family record, and sets the user-visible family ID to the value in the $variable.