How do I avoid orphan data?

[DrChocolate]

Greetings: Here's the background: Using Access 2010, I'm constructing a database (eventually to be connected with a MySQL or similar web site) to keep track of support group data. The groups and the contacts that the public uses to learn about each group have a many to many relationship (each group can have more than one contact and each contact can "belong" to more than one group). Each contact can have one or more "routes" (ways to contact them such as a phone number).

Here's the problem at hand: I'm concerned that as I mark a contact as inactive, I could easily leave a group without a contact. Along the same trail, when I inactivate a "route" (e.g., a contact's phone number), I could easily leave the contact and/or the group without a phone number to use. I don't expect Access to find a group a new contact person, but, for example, before I record the contact's wish to resign, I do want to know that his disappearing will leave the group without a contact.

I'll be gathering this information on the phone with the contacts for the groups. I'm open to changes in table structure, queries that would reveal current status, warning pop-ups that I'm getting too close to an orphanage, or whatever. I realize that the attached table structure (in its entirety) may have bigger problems than orphan data, but I needed to start somewhere.

Please have sympathy on this newbie Access user learning about many to many relationships. Thank you.

 

[tehNellie]

If you're using an active/inactive flag you aren't leaving a group without a contact. If you have referential integrity on your relationships you can't DELETE that Contact record from the Table if it belongs to a [number of] Groups and that would leave the Group with an orphan reference to the contact table, but access wont "care" that you set the Contact(s) Inactive, they still belong the to the Group and keep referential integrity valid.

Your group might not have an Active Contact, but it does have a contact so it's not an orphan Record in the traditional Sense.

In Access I'm not sure that there is anything you can easily do at a table level to ensure that an Entity in one table has a record you consider active in another, you would basically need to check in your application before you inactivate a record that it won't violate your business rules (ie that a Group must have at least one active Contact.).